oracle 19c单机adg搭建

前提：主库安装数据库，备库只安装oracle软件即可。
若主库包含PDB，会将PDB和CDB一同复制到备库
1、DG基础环境
oracle 主库ip：192.168.220.11 ORACLE_SID:orcl
db_name=‘orcl’ 主机名：oracle19c
SERVICE_NAME = orcl

oracle 备库ip：192.168.220.12 ORACLE_SID：orcldg db_name=‘orcl’ 主机名：oracle19cdg
SERVICE_NAME = orcldg

2、主库和备库监听配置以及TNS配置(主备库tns一样)
主库listener.ora

[oracle@oracle19c admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
        (GLOBAL_DBNAME=orcl)
        (ORACLE_HOME=/u01/app/oracle/product/19/db_1)
        (SID_NAME=orcl)
    )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

备库listener.ora

[oracle@oracle19cdg admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
        (GLOBAL_DBNAME=orcldg)
        (ORACLE_HOME=/u01/app/oracle/product/19/db_1)
        (SID_NAME=orcldg)
    )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19cdg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

主备库tnsnames.ora

[oracle@oracle19cdg admin]$ cat tnsnames.ora 
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

orcldg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )

通过tnsping测试配置文件是否正常

3、主库需进行的修改

#开启归档
alter database force logging;
alter database archivelog;

#参数设置
alter system set log_archive_config='dg_config=(orcl,orcldg)';
alter system set log_archive_dest_1='location=/gisapp/archive lgwr valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
alter system set log_archive_dest_2='service=orcldg lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
DB_FILE_NAME_CONVERT--未设置
alter system set LOG_FILE_NAME_CONVERT='/u01/data/ORCL','/u01/data/ORCL' scope=spfile sid='*';

alter system set log_archive_max_processes=5;
alter system set standby_file_management=auto;
alter system set log_archive_dest_state_1='enable';
alter system set log_archive_dest_state_2='enable';
alter system set fal_server='orcldg' scope=spfile;
alter system set fal_client='orcl' scope=spfile;

#主库添加standby redo logfile(连接到CDB$ROOT中执行)
添加规则：创建standby日志组，个数是源日志个数+1再与实例数的积，size不能小于原来日志的大小

SQL> select thread#,group#,members,bytes,bytes/1024/1024 from v$log;

   THREAD#     GROUP#	 MEMBERS      BYTES BYTES/1024/1024
---------- ---------- ---------- ---------- ---------------
	 1	    1	       1  209715200		200
	 1	    2	       1  209715200		200
	 1	    3	       1  209715200		200


alter database add standby logfile group 4 ('/u01/data/ORCL/redo04.log') size 200M;
alter database add standby logfile group 5 ('/u01/data/ORCL/redo05.log') size 200M;
alter database add standby logfile group 6 ('/u01/data/ORCL/redo06.log') size 200M;
alter database add standby logfile group 7 ('/u01/data/ORCL/redo07.log') size 200M;

SQL> select group#,status,type,member from v$logfile;

#主库创建pfile以及cpoy创建好的pfile和口令文件到备库对应位置
主库：
create pfile=1.ora from spfile;
cd $ORACLE_HOME/dbs
scp 1.ora oracle@oracle19cdg:/u01/app/oracle/product/19/db_1/dbs
scp orapworcl oracle@oracle19cdg:/u01/app/oracle/product/19/db_1/dbs/orapworcldg

4、备库进行的操作

#需要确认和修改的参数
[oracle@oracle19cdg dbs]$ cat 1.ora 
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.control_files='/u01/data/ORCLDG/control01.ctl','/u01/data/ORCLDG/control02.ctl'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.fal_client='orcldg'
*.fal_server='orcl'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/arch lgwr valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_max_processes=5
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

#备库用1.ora创建spfile，启动到nomount
create spfile from pfile='/u01/app/oracle/product/19/db_1/dbs/1.ora'
startup nomount;

#duplicate开始，在备库通过rman进行复制备库
rman target sys/oracle@ORCL auxiliary sys/oracle@ORCLDG

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate auxiliary channel c4 type disk;
allocate auxiliary channel c5 type disk;
allocate auxiliary channel c6 type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
或者：duplicate target database for standby from active database nofilenamecheck dorecover;

#查看备库数据库状态
select open_mode from v$database;
OPEN_MODE
-------------
MOUNTED

把备库启动到open下：
alter database open;
在备库启动数据库到恢复管理模式，并开始准备从主库接受日志的传输：
alter database recover managed standby database using current logfile disconnect from session;

#检查进程信息
#主库需要LNS进程，备库需要RFS,MRP进程
select process,status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH	  CONNECTED
DGRD	  ALLOCATED
DGRD	  ALLOCATED
ARCH	  CONNECTED
ARCH	  CONNECTED
ARCH	  CONNECTED
ARCH	  CONNECTED
RFS	  IDLE
RFS	  IDLE
RFS	  IDLE
RFS	  IDLE
MRP0	  APPLYING_LOG

ALLOCATED: 正在准备但还未连接主库
ATTACHED: 正在连接到主库
CONNECTED:已经连接到主库
IDLE:空闲
ERROR：失败的进程，需要关注
RECEIVING:归档日志接收中
OPENING:归档日志处理中
CLOSING:归档日志处理完，正在收尾中
WRITING: 进程在将REDO数据写向归档文件中
WAIT_FOR_LOG:等待新的REDO归档数据中
WAIT_FOR_GAP:归档有中断，正在等待中断的那部分REDO数据.
APPLYING_LOG:正在应用REDO归档数据到备库

#检查主备库角色和其他信息
备库：
select database_role,protection_mode,open_mode from v$database;
主库：
select database_role,open_mode from gv$database;

#在备库查看data guard为那种日志接受方式：
select process,client_process,sequence#,status from v$managed_standby;

#检查主备库sequence#
select max(sequence#) from v$archived_log;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc;

#检查日志队列是否正常传输
select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
#检查两边的日志同步情况：
select sequence# from v$archived_log where applied='YES';

#备库查询重做数据传输和应用滞后
set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');