从无库建立ADG

最新推荐文章于 2025-12-22 22:49:51 发布

原创最新推荐文章于 2025-12-22 22:49:51 发布 · 398 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#eclipse

一、主库建立

1、实例名：primary

2、redo 100M/个 5组（在下面）

3、归档路径 /u01/arch

mkdir /u01/arch -p

4、数据目录 /u01/datafile

mkdir /u01/datafile -p

5、online 日志/u01/online, 100M/个 5组

mkdir /u01/online/primary/ -p

ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/online/primary/redo04_1.log', '/u01/online/primary/redo04_2.log') SIZE 100M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/online/primary/redo05_1.log', '/u01/online/primary/redo05_2.log') SIZE 100M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/online/primary/redo06_1.log', '/u01/online/primary/redo06_2.log') SIZE 100M;
ALTER DATABASE ADD LOGFILE GROUP 7 ('/u01/online/primary/redo07_1.log', '/u01/online/primary/redo07_2.log') SIZE 100M;
ALTER DATABASE ADD LOGFILE GROUP 8 ('/u01/online/primary/redo08_1.log', '/u01/online/primary/redo08_2.log') SIZE 100M;

-- 检查
SELECT 
    l.GROUP#,
    l.SEQUENCE#,
    l.BYTES/1024/1024 AS SIZE_MB,
    l.MEMBERS,
    l.STATUS AS GROUP_STATUS,
    lf.MEMBER,
    lf.STATUS AS MEMBER_STATUS
FROM 
    V$LOG l
JOIN 
    V$LOGFILE lf ON l.GROUP# = lf.GROUP#
ORDER BY 
    l.GROUP#, lf.MEMBER;
    
#切换日志
alter system switch logfile;
#检查点
ALTER SYSTEM CHECKPOINT;
-- 删除原组
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

6、tnsname.ora配置:pri.sty

cd $ORACLE_HOME/network/admin/
vi tnsnames.ora
-------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = echodddb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

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

7、监听

cd $ORACLE_HOME/network/admin/

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = echodddb)(PORT = 1521))
    )
  )
  
SID_LIST_LISTENER =
 (SID_LIST =
     (SID_DESC =
         (GLOBAL_DBNAME = primary)
         (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
         (SID_NAME = primary)
      )
 )

ADR_BASE_LISTENER = /u01/app/oracle

8、启用归档模式

-- 如未启用归档则执行以下命令
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FORCE LOGGING;  -- 确保所有操作都记录日志
ALTER DATABASE OPEN;

-- 验证，强制日志、归档
SELECT force_logging, log_mode FROM v$database;

9、配置Data Guard参数

-- 用于区分主备库，两者必须不一致，主库可以不用修改，默认与sid一致
ALTER SYSTEM SET db_unique_name='primary' SCOPE=SPFILE;

-- 定义 ADG 环境成员列表，使用 db_unique_name 表示
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)' SCOPE=BOTH;

-- 一般用于设置本地归档路径
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' SCOPE=BOTH;

-- 一般用于指向 ADG 备库的远程归档路径，指向备库的 TNS
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sty LGWR ASYNC COMPRESSION=enable VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' SCOPE=BOTH;

-- 主库创建数据文件时，备库自动创建，需修改为 AUTO
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

-- 出现 GAP 时自动获取归档日志，配置为 ADG 对端 TNS
ALTER SYSTEM SET FAL_SERVER=sty SCOPE=BOTH;

-- 写自身 TNS
ALTER SYSTEM SET FAL_CLIENT=pri SCOPE=BOTH;

-- 数据文件路径转换，'远端路径','本地路径'
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/datafile/standby/','/u01/datafile/primary/' SCOPE=SPFILE;

-- redo日志文件路径转换，'远端路径','本地路径'
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/online/standby/','/u01/online/primary/' SCOPE=SPFILE;

-- 重启使SPFILE参数生效
SHUTDOWN IMMEDIATE;
STARTUP;

-- 检查
show parameter LOG_ARCHIVE_CONFIG
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter STANDBY_FILE_MANAGEMENT
show parameter db_unique_name
show parameter FAL_SERVER
show parameter FAL_CLIENT
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter REMOTE_LOGIN_PASSWORDFILE

10、创建备库pfile

create pfile='/home/oracle/initprimary.ora' from spfile;

scp /home/oracle/initprimary.ora oracle@echodddg:/home/oracle/initstandby.ora

11、日志

col member for a50
col status for a10
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status 
from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status
from v$standby_log a,v$logfile b where a.group#=b.group# order by 3,1,2;

-- 添加
alter database add standby logfile group 101 ('/u01/online/primary/std_redo101_1.log','/u01/online/primary/std_redo101_2.log') size 100M;
alter database add standby logfile group 102 ('/u01/online/primary/std_redo102_1.log','/u01/online/primary/std_redo102_2.log') size 100M;
alter database add standby logfile group 103 ('/u01/online/primary/std_redo103_1.log','/u01/online/primary/std_redo103_2.log') size 100M;
alter database add standby logfile group 104 ('/u01/online/primary/std_redo104_1.log','/u01/online/primary/std_redo104_2.log') size 100M;
alter database add standby logfile group 105 ('/u01/online/primary/std_redo105_1.log','/u01/online/primary/std_redo105_2.log') size 100M;
alter database add standby logfile group 106 ('/u01/online/primary/std_redo106_1.log','/u01/online/primary/std_redo106_2.log') size 100M;

12、密码文件

scp $ORACLE_HOME/dbs/orapwprimary oracle@echodddg:$ORACLE_HOME/dbs/orapwstandby

二、备库实例名:standby

1、修改参数文件

primary.__large_pool_size=100663296
primary.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=1291845632
primary.__sga_target=1929379840
primary.__shared_io_pool_size=0
primary.__shared_pool_size=402653184
primary.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/datafile/standby/control01.ctl','/u01/app/oracle/fast_recovery_area/standby/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/datafile/primary/','/u01/datafile/standby/'
*.db_name='primary'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='standby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.fal_client='STY'
*.fal_server='PRI'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=/u01/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=pri LGWR ASYNC COMPRESSION=enable VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/online/primary/','/u01/online/standby/'
*.memory_target=3221225472
*.open_cursors=300
*.proechoddses=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

2、创建目录

mkdir -p /u01/app/oracle/admin/standby/adump
mkdir -p /u01/datafile/standby/
mkdir -p /u01/app/oracle/fast_recovery_area/standby/
mkdir -p /u01/arch/
mkdir -p /u01/online/standby/

监听

cd $ORACLE_HOME/network/admin/

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = echodddg)(PORT = 1521))
    )
  )
  
SID_LIST_LISTENER =
 (SID_LIST =
     (SID_DESC =
         (GLOBAL_DBNAME = standby)
         (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
         (SID_NAME = standby)
      )
 )

ADR_BASE_LISTENER = /u01/app/oracle

-- 启动
lsnrctl start
-- 状态
lsnrctl status

4、tns

cd $ORACLE_HOME/network/admin/
vi tnsnames.ora
-------------
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = echodddb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )

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

5、修改环境变量

6、启动到nomount

. .bash_profile
startup nomount pfile='/home/oracle/initstandby.ora';

7、检查

show parameter LOG_ARCHIVE_CONFIG
show parameter LOG_ARCHIVE_DEST_1
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_STATE_1
show parameter LOG_ARCHIVE_DEST_STATE_2
show parameter STANDBY_FILE_MANAGEMENT
show parameter db_unique_name
show parameter FAL_SERVER
show parameter FAL_CLIENT
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter REMOTE_LOGIN_PASSWORDFILE

8、创建spfile

create spfile from pfile='/home/oracle/initstandby.ora';
shutdown immediate;
startup nomount
show parameter spfile

9、验证连接

sqlplus sys/oracle@pri as sysdba
sqlplus sys/oracle@sty as sysdba

三、创建备库（DUPLICATE）

1、测试连接

-- 测试连接
rman target sys/oracle@pri auxiliary sys/oracle@sty

2、编写RMAN复制脚本

cat /home/oracle/adg_dup.sh 
------------下面是脚本-------------
export ORACLE_SID=standby
rman target sys/oracle@pri auxiliary sys/oracle@sty log='/home/oracle/adg_dup.log'<<EOF
run {
allocate channel prmy1 type disk rate 100M; 
allocate channel prmy2 type disk rate 100M;
allocate channel prmy3 type disk rate 100M; 
allocate channel prmy4 type disk rate 100M;
allocate auxiliary channel stby1 type disk rate 100M; 
duplicate target database for standby from active database dorecover nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel prmy3;
release channel prmy4;
release channel stby1;
}
exit
EOF

3、执行bash adg_dup.sh

四、启动日志应用和验证

1.备库启动 MRP 数据同步进程

alter database recover managed standby database using current logfile disconnect from session;
set lines 222 pages 222
col NAME for a32
col VALUE for a22
col TIME_COMPUTED for a25
col DATUM_TIME for a25
select name,value,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;


执行root.sh时遇上bug导致CRS-2101问题的解决方法-优快云博客 https://blog.youkuaiyun.com/shayuwei/article/details/90483029

CRS-2101 The OLR was formatted using version 3_crs-2101:the olr was formatted using version 3.-优快云博客 
https://blog.youkuaiyun.com/yabingshi_tech/article/details/128531871

col PROechoddS for a11
col STATUS for a15
col CLIENT_PROechoddS for a11
col CLIENT_PID for a11
col GROUP# for a5
col SEQUENCE# for 99999999
col BLOCK# for 99999999
col BLOCKS for 99999999
col DELAY_MINS for 99999999
col THREAD# for 99
select PROechoddS,PID,STATUS,CLIENT_PROechoddS,CLIENT_PID,GROUP#,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;

2.数据同步后，关闭 MRP 进程，备库打开到 OPEN READ ONLY 状态，重新启动 MRP 进程（adg）

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;

3. 验证同步状态

-- 在主库查询
SELECT DEST_ID, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# 
FROM V$ARCHIVE_DEST_STATUS 
WHERE DEST_ID=2;

-- 在备库查询
SELECT PROechoddS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 
FROM V$MANAGED_STANDBY;

col PROTECTION_MODE for a30
col PROTECTION_LEVEL for a30
col DATABASE_ROLE for a30
col SWITCHOVER_STATUS for a30
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE, SWITCHOVER_STATUS 
FROM V$DATABASE;

select name,open_mode from v$database;

-- 主库检查传输状态
col DEST_NAME for a20
SELECT DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

-- 备库检查应用延迟
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Received", 
       APPL.SEQUENCE# "Last Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Gap"
FROM
(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG 
 WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#, SEQUENCE# FROM V$LOG_HISTORY 
 WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;

-- 检查进程
ps -ef | grep -i nsa   或者nss

4. 日志切换测试

-- 在主库执行
ALTER SYSTEM SWITCH LOGFILE;
-- 观察备库alert日志确认接收和应用情况
-- 查看日志
col member for a50
col status for a10
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status 
from v$log a,v$logfile b where a.group#=b.group#
union all
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status
from v$standby_log a,v$logfile b where a.group#=b.group# order by 3,1,2;

五、备库宕机后启动测试

1、模拟备库宕机

1）宕机

shutdown immediate

2）查看日志（备库）

3）查看日志（主库）报错：LOG_ARCHIVE_DEST_2无法归档

2、主库继续运行并模拟业务

1）插入数据

2）日志只有切换redo

3、启动备库

startup

4、观察主库

5、设置错误的参数（没有发现RFS）

6、改回参数后重启

-- 出现 GAP 时自动获取归档日志，配置为 ADG 对端 TNS
ALTER SYSTEM SET FAL_SERVER=pri SCOPE=BOTH;

-- 写自身 TNS
ALTER SYSTEM SET FAL_CLIENT=sty SCOPE=BOTH;

shutdown immediate
startup