ORACLE-DG总结

一位摩羯座DBA

已于 2023-08-23 14:45:21 修改

阅读量981

点赞数

文章标签： oracle 数据库运维

于 2023-08-03 18:13:10 首次发布

本文链接：https://blog.csdn.net/BABAJack/article/details/132089525

版权

一：两台ORACLE单机搭建DG

前期准备

主库：安装软件&监听&数据库
备库：只安装数据库软件&监听
注意：DB_NAME 参数主库和备库必须相同
DB_UNIQUE_NAME 参数主库和备库必须不同

配置互信

两个节点都要做
vi etc/hosts （）
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.210.19 dg1
192.168.210.20 dg2

1.1 开启归档

archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter system set log_archive_dest_1='location=/u01/archivelog';

在这里插入图片描述

1.2 设置数据库闪回和大小

创建闪回路径
su - oracle
mkdir -p /u01/db_recovery_file_dest

select flashback_on from v$database;
alter system set db_recovery_file_dest_size='5G';
alter system set db_recovery_file_dest='/u01/db_recovery_file_dest';
alter database flashback on;
show parameter db_recovery;

在这里插入图片描述

1.3 强制开启归档

select force_logging from v$database;
alter database force logging;

在这里插入图片描述

1.4 添加standby日志（比在线日志多一个）

select group#,bytes/1024/1024 from v$log;
查出来3个，添加4个，standby log x+1

在这里插入图片描述

alter database add standby logfile group 4 '/u01/app/oracle/fast_recovery_area/orcl/onlinelog/standby4_redo_01.log' size 200M;
alter database add standby logfile group 5 '/u01/app/oracle/fast_recovery_area/orcl/onlinelog/standby5_redo_02.log' size 200M;
alter database add standby logfile group 6 '/u01/app/oracle/fast_recovery_area/orcl/onlinelog/standby6_redo_03.log' size 200M;
alter database add standby logfile group 7 '/u01/app/oracle/fast_recovery_area/orcl/onlinelog/standby7_redo_04.log' size 200M;
select group#,bytes/1024/1024 from v$standby_log;

在这里插入图片描述

1.5 修改参数文件

alter system set log_archive_config='DG_CONFIG=(orcl,orcl02)';
alter system set log_archive_dest_1='location=/u01/archivelog';
alter system set log_archive_dest_2='SERVICE=orcl02 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl02' scope=spfile;
alter system set log_archive_dest_state_1='enable';
alter system set log_archive_dest_state_2='enable';
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl02/datafile','/u01/app/oracle/oradata/orcl/datafile' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl02/onlinelog','/u01/app/oracle/oradata/orcl/onlinelog' scope=spfile;
alter system set db_unique_name='orcl' scope=spfile;
alter system set fal_server='orcl02';
alter system set fal_client='orcl';
alter system set standby_file_management='AUTO';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

在这里插入图片描述

1.6 修改主库监听文件

cd $ORACLE_HOME/network/admin
vim listener.ora

在这里插入图片描述

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = orcl)
 (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
 (SID_NAME = orcl)
 )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.19)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

1.7 配置主库tns配置文件

vi tnsnames.ora

orcl =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.19)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl)
 )
 )

orcl02 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.20)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl02)
 )

1.8 重启主库监听

lsnrctl stop
lsnrctl start
lsnrctl status

在这里插入图片描述

1.9 拷贝主库参数文件到备库

SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
mv initorcl.ora initorcl02.ora
cp orapworcl orapworcl02
scp initorcl02.ora,orapworcl02 oracle@192.168.210.20:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/
rm -f initorcl02.ora
rm -f orapworcl02

在这里插入图片描述

2.0 修改拷贝到备库的参数文件

将主备库参数位置更换

orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=1778384896
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=0
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=822083584
orcl.__sga_target=2466250752
orcl.__shared_io_pool_size=134217728
orcl.__shared_pool_size=520093696
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl02/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/orcl02/controlfile/o1_mf_ld90cvpr_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/datafile','/u01/app/oracle/oradata/orcl02/datafile'
*.db_name='orcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/db_recovery_file_dest'
*.db_unique_name='orcl02'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl02XDB)'
*.enable_pluggable_database=true
*.fal_client='orcl02'
*.fal_server='orcl'
*.local_listener='LISTENER_ORCL'
*.log_archive_config='DG_CONFIG=(orcl02,orcl)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/onlinelog','/u01/app/oracle/oradata/orcl02/onlinelog'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=782m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2346m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

在这里插入图片描述

创建参数对应的相关文件夹

mkdir -p /u01/app/oracle/oradata/orcl02/controlfile/
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/oradata/orcl/datafile
mkdir -p /u01/app/oracle/oradata/orcl02/datafile
mkdir -p /u01/db_recovery_file_dest
mkdir -p /u01/app/oracle/oradata/orcl/
mkdir -p /u01/app/oracle/oradata/orcl02/

2.1 配置备库监听

cd $ORACLE_HOME/network/admin
vim listener.ora

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

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = orcl02)
 (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
 (SID_NAME = orcl02)
 )
)
 
ADR_BASE_LISTENER = /u01/app/oracle 
ADR_BASE_LISTENER = /u01/app/oracle

2.1 配置备库tns

vim tnsnames.ora

orcl =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.19)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl)
 )
 )

orcl02 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.20)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = orcl02)
 )
 )

2.2 启动备库监听

lsnrctl start

在这里插入图片描述

2.2 启动备库到nomount状态

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initorcl02.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initorcl02.ora';

在这里插入图片描述

2.3 测试tns远程连接数据库

tnsping orcl
tnsping orcl02
sqlplus sys/******@orcl as sysdba
show parameter name;
sqlplus sys/******@orcl02 as sysdba
show parameter name;

在这里插入图片描述

2.4 主库RMAN备份文件并传输到备库

mkdir -p /home/oracle/bak/
rman target /
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
backup as compressed backupset database format='/home/oracle/bak/%d_%s.DB';
sql 'alter system archive log current';
}
BACKUP DEVICE TYPE DISK FORMAT '/home/oracle/bak/st_%U' CURRENT CONTROLFILE FOR STANDBY;

scp * oracle@192.168.210.20:/home/oracle/bak/

在这里插入图片描述

2.5 备库先恢复控住文件，在恢复数据文件

1）第一步恢复参数文件
rman target  /
startup nomount;
2）恢复控制文件
restore controlfile  from "/home/oracle/bak/st_0e24elph_1_1" ;

恢复完后改成mount状态
alter database mount;

3)转储被损坏的文件
 restore database;

4) 通过redo log，archive log恢复的
recover database;

## 5) 打开数据库
alter database open resetlogs;

在这里插入图片描述

2.6 启动备库DG

(13) 备库先恢复控住文件，在恢复数据文件
启动DG
shutdown immediate;
startup mount
alter database recover managed standby database using current logfile disconnect from session;
取消DG
alter database recover managed standby database cancel;

在这里插入图片描述

2.7 开启主库实时同步

(15)日志切换
alter system ser LOG_ARCHIVE_DEST_STATE_2=enable;
alter system switch logfile;
检查主备日志序列号
SQL> select max(sequence#) from v$archived_log;

SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
3.10开启闪回
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;

ORACLE DG参考

https://cloud.tencent.com/developer/article/2274828
https://zhuanlan.zhihu.com/p/358316546 ##DG扩容
https://www.modb.pro/db/612129
http://www.manongjc.com/detail/62-kxsvvckhkkbaggv.html DG搭建