oracle19c搭建DG
DataGuard环境的重启原则是:先关主库再关备库,先开备库再开主库
1.安装主库,备库
前面的博文有19c通过rpm安装的方式,备库安装后有CDB,PDB,先把他们先删除了。
2.删除备库的数据库
--查看数据库状态
select status from v$instance;
--关闭数据库(如果数据库是开启的话)
alter database close;
--拒绝新的连接
alter system enable restricted session;
--删除数据库
drop database;
--编辑配置文件
vim /etc/oratab
--删除改行,或者把最后改成N
ORCLABC:/opt/oracle/product/19c/dbhome_1:N
--删除数据库的数据文件
rm -rf /opt/oracle/oradata/ORCLCDB
--删除相关监听文件里面的数据库信息
3.配置
ip 主机名 sid unique_name
主库:192.168.12.136 centos orclpdb1 oracle
备库:192.168.12.140 centos_dg orclpdb1 oracle_dg
3.1配置host
vim /etc/hosts
3.2配置主备库监听
--主库监听
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclpdb1)
(ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
(SID_NAME=ORCLCDB)
)//注意,这里一定要有一个空格,不然启动不起来,卧槽
(SID_DESC=
(GLOBAL_DBNAME=ORCLCDB)
(ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
(SID_NAME=ORCLCDB)
)//注意,这里一定要有一个空格,不然启动不起来,卧槽
)
SID_NAME:实例名称,单机版就一个实例
GLOBAL_DBNAME:对应数据库名称,如PDB/CDB的名称
--备库监听
--只能对CDB对DG,pdb本身也就包含在CDB里面
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCLCDB)
(ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
(SID_NAME=ORCLCDB)
) //注意,这里一定要有一个空格,不然启动不起来,卧槽
)
3.3配置主备库tnsnames.ora
ORACLE_PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ORCLCDB)
(SERVER = DEDICATED)
) //注意,这里一定要有一个空格,不然启动不起来,卧槽
)
ORACLE_STY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = centos_dg)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ORCLCDB)
(SERVER = DEDICATED)
) //注意,这里一定要有一个空格,不然启动不起来,卧槽
)
配置完成后使用tnsping测试一下
3.4 开启归档 以及强制日志
查看归档是否开启
archive log list
修改归档路径
alter system set log_archive_dest_1='location=/data/oracle/archive';
启用归档
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
alter system switch logfile;
查看是否开启强制日志
select name,log_mode,force_logging from v$database;
开启强制日志
alter database force logging;
3.5 拷贝密码文件
su - oracle
mkdir /data/oradata/rman_backup
cd $ORACLE_HOME/dbs
cp orapworacle /data/oradata/rman_backup
3.6主库创建standby redo log
--查看日志文件
select * from v$logfile;
--查看当前日志组状态
select group#,sequence#,members,bytes/1024/1024,status from v$log order by GROUP#;
--添加 standby redo log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/oradata/standby/redo04.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/oradata/standby/redo05.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data/oradata/standby/redo06.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data/oradata/standby/redo07.log') size 200M;
3.7主库修改参数文件
--备份参数文件
create pfile='/data/oradata/rman_backup/pfile.ora.1bak' from spfile;
--更改db_unique_name
show parameter db_unique_name;
alter system set db_unique_name='oracle_pri' scope=spfile;
shutdown immediate;
startup
show parameter db_unique_name;
--更改其他参数
---初始化参数LOG_ARCHIVE_CONFIG用于控制发送归档日志到远程位置、接收远程归档日志
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oracle_pri,oracle_sty)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle_pri' scope=both sid='*';
--两种日志传输方式(ARC和LGWR)
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oracle_sty LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle_sty'scope=both sid='*';
--FAL指获取归档日志(Fetch Archived Log)
alter system set FAL_SERVER='oracle_sty' scope=both sid='*';
alter system set fal_client='oracle_pri' scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/data/oradata/ORACLE','/data/oradata/ORACLE' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/data/oradata/ORACLE','/data/oradata/ORACLE' scope=spfile sid='*';
--重启数据库
shutdown immediate;
startup;
--生成新的参数文件
create pfile='/data/oradata/rman_backup/pfile.ora.2bak' from spfile;
3.8 主库配置最大性能模式
--查看模式(默认情况是最大性能模式)
select database_role,protection_mode,protection_level,open_mode from v$database;
--修改模式为最大性能模式
alter database set standby database to MAXIMIZE PERFORMANCE;
--题外,切换模式
1.主库启动到mount状态
startup mount
2.主库切换模式
alter database set standby database to MAXIMIZE Availability;
3.9 创建备库控制文件
alter database create standby controlfile as '/data/oradata/rman_backup/standby.ctl';
--生成完关闭数据库
shutdown immediate;
3.10 主库RMAN备份数据库到备库
rman target '"/as sysbackup"'
--rman备份脚本,备份数据文件和归档文件
RMAN> run{
allocate channel c1 type disk maxpiecesize=100m;
allocate channel c2 type disk maxpiecesize=100m;
backup database filesperset 4 format '/data/oradata/rman_backup/ora_L0_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/data/oradata/rman_backup/arch_L0_%d_%T_%s_%p' delete input;
crosscheck backup;
crosscheck archivelog all;
}
--把数据复制到备库上去
scp -r ./rman_backup/ oracle@centos_dg:/data/oradata/
3.11 备库基础配置
su - oracle
mkdir -p /data/oradata/ORACLE
mkdir -p /data/oracle/archive/ORACLE
mkdir -p /data/oradata/rman_backup
mkdir -p /data/oracle/standby
--把上传的主库的密码文件放到备库的dbs下面去
mv /data/oradata/rman_backup/orapwORCLCDB $ORACLE_HOME/dbs/;
--把上传的主库的控制文件提取出来
cp /data/oradata/rman_backup/standby.ctl /data/oradata/ORACLE/control01.ctl
cp /data/oradata/ORACLE/control01.ctl /data/oradata/ORACLE/control02.ctl
3.12 修改参数文件(pfile.ora.2bak)
vim pfile.ora.2bak
主要就是改db名字,oracle_pri改成oracle_sty
3.13 启动备库到nomount状态
sqlplus / as sysdba
--使用刚改的那个配置文件
startup pfile = '/data/oradata/rman_backup/pfile.ora.2bak' nomount
--创建spfile
create spfile from pfile='/data/oradata/rman_backup/pfile.ora.2bak';
3.14 恢复控制文件
rman target '"/as sysbackup"'
--这个一执行完,之前删除的oradata下面的ORCLCDB文件加就又被创建了
restore controlfile from '/data/oradata/ORACLE/control01.ctl';
3.14 切换数据库到mount状态并恢复主库数据
alter database mount;
catalog start with '/data/oradata/rman_backup/';
run {
allocate channel c1 type disk maxpiecesize=100m;
allocate channel c2 type disk maxpiecesize=100m;
restore database ;
}
3.15 重新启动备库
startup nomount;
alter database mount standby database;
--开启备库
--取消应用日志的接收
alter database recover managed standby database cancel;
alter database open read only;
--开启备库应用日志:standby 端开启实时日志应用,会启动 MRP0 日志应用进程
alter database recover managed standby database using current logfile disconnect from session;
--关闭备库的时候也要先取消应用日志接受,再关闭
alter database recover managed standby database cancel;
--打开实时应用状态模式
alter database recover managed standby database using current logfile disconnect;
查看主备库GAP(日志间隙),配置好fal_server,fal_client两个参数gap问题可以自动解决,不需人工干预
select status,gap_status from v$archive_dest_status;
或
select * from v$archive_gap;
//若有的话,找到那些没有传过来的日志文件
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; (备库)
//在主库找到相关文件
SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG where dest_id = 1;(主库,location本地在dest1上时)
//手工拷贝到standby端,再注册
ALTER DATABASE REGISTER LOGFILE ‘/data/oracle/archive/1_34_1118256257.dbf’;
//重新开始管理恢复
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
查看主备库的进程状态(备库独有RFS)
select process, sequence#, status, delay_mins from v$managed_standby;
RFS:RFS(Remote File Server)进程主要用来接受从主库传送过来的日志信息。对于物理备库而言,RFS进程可以直接将日志写进Standby Redo logs,也可以直接将日志信息写到归档日志中
LNS:LNSn(LGWR Network Server process)DG可以使用ARCn、LGWR来传送日志,但它们都是把日志发送给本地的LNSn(如果有多个目标备库,那么会启动相应数量的LNSn进程,同时发送数据)进程,然后备库的RFS进程接收数据,接收到的数据可以存储在备库的备用Redo日志文件中或备库的归档日志中,然后再应用到备库中。
MRP:MRP(Managed Recovery Process)进程只针对物理备库,作用为应用从主库传递过来的Redo日志到物理备库,称为Redo Apply。如果使用SQL语句“ALTER DATABASE RECOVER MANAGED STANDBY DATABASE”启用该进程,那么前台进程将会做恢复。如果加上DISCONNECT语句,那么恢复过程将在后台进程,发出该语句的进程可以继续做其它的事情
(实时应用是用LNS进程发送日志,非实时应用是用LGWR或者ARCH进程发送日志)
备库查看归档日志是否被应用
select thread#,sequence#,first_time,next_time,applied from v$archived_log;
备库删除归档文件脚本
run{
allocate channel d1 type disk;
delete noprompt archivelog all completed before ‘sysdate-3’;
delete noprompt obsolete;
release channel d1;
}