暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 19c Data Guard部署 for Centos7.9

Oracle 19c Data Guard部署 for Centos7.9

keyhappy 2024-04-16
505

版本号：v1.0

1.0     操作系统安装

注：预防后面会出现乱码，请选择English

按实际情况设置本地时区

注：因为后面升级Linux内核时会占用此分区的空间，所以boot分区不要设得太小，建议设置为512M以上。

swap设置为内存的两倍，如4G的内存则设为8G

 

 

剩余的空间全部分给根

根据实例情况设定固定IP及DNS

2.0 主数据库安装

2.1 数据库安装

#软件下载

#https://www.oracle.com/cn/database/technologies/oracle19c-linux-downloads.html

#https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/index.html

#查找：oracle-database-preinstall-19c找到最新版本的下载链接如下：

https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm

 

#最后拼接代码，执行如下进行预安装，以下安装会自动创建oracle、oinstall等用户和组：

yum install -y https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm

 

#开始安装Oracle

yum install oracle-database-ee-19c-1.0-1.x86_64.rpm

#修改SID，模板名称，字符集，PDB的名称，是否为CDB等：

vi /etc/init.d/oracledb_ORCLCDB-19c

export ORACLE_VERSION=19c

export ORACLE_SID=ORCLCDB

export TEMPLATE_NAME=General_Purpose.dbc

export CHARSET=AL32UTF8

export PDB_NAME=ORCLPDB1

export LISTENER_NAME=LISTENER

export NUMBER_OF_PDBS=1

export CREATE_AS_CDB=true

 

#配置hosts，主机名必须要有正确的IP解释，不可以是127.0.0.1

vi /etc/hosts

192.168.80.100 r19c r19c.db

 

#注：如果没有配置以上host文件配置，将会有一个离奇的报错：

/etc/init.d/oracledb_ORCLCDB-19c configure

Configuring Oracle Database ORCLCDB.

[FATAL] [DBT-06103] The port (1,521) is already in use.

   ACTION: Specify a free port.

#明明端口没有占用，硬说被占用了

 

#创建数据库

/etc/init.d/oracledb_ORCLCDB-19c configure

 

#配置用户环境变量，内容应与前面的配置文件相匹配(oracledb_ORCLCDB-19c)

su – oracle

vi .bash_profile

export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

export ORACLE_SID=ORCLCDB

export PATH=$ORACLE_HOME/bin:$PATH

 

source .bash_profile

sqlplus / as sysdba

lsnrctl status

 

#测试环境关闭防火墙及selinux

systemctl stop firewalld

systemctl disable firewalld

systemctl status firewalld

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

setenforce 0

getenforce

2.2 Data Guard的主库配置

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html

2.2.1打开归档

shutdown immediate

startup mount

alter database archivelog;

#确认打开结果

archive log list;

select log_mode,force_logging from v$database;

 

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

alter system set db_recovery_file_dest='/opt/oracle/fast_recovery_area/ORCLCDB';

2.2.2 打开强制日志

# force logging属性，数据库改为force logging后，nologging和append hint会自动失效（甚至连SQL loader命令的直接路径导入也会自动转化为常规路径），这种这状态下可以确保数据库中所有操作都会记录重做信息。

alter database force logging;

select log_mode,force_logging from v$database;

2.2.3 选择同步模式

#两种模式选择，性能优先还是数据优先？

#数据优先

alter database set standby nologging for data availability;

#性能优先

alter database set standby nologging for load performance;

2.2.4 主库添加附加日志

#

alter database add standby logfile group 4 ('/opt/oracle/oradata/ORCLCDB/stredo04.log') size 100m;

alter database add standby logfile group 5 ('/opt/oracle/oradata/ORCLCDB/stredo05.log') size 100m;

alter database add standby logfile group 6 ('/opt/oracle/oradata/ORCLCDB/stredo06.log') size 100m;

alter database add standby logfile group 7 ('/opt/oracle/oradata/ORCLCDB/stredo07.log') size 100m;

select group#, thread#, sequence#, status from v$standby_log;

alter database drop standby logfile group 21;

 

alter database add standby logfile thread 1 group 10 size 50m;

alter database add standby logfile thread 1 group 11 size 50m;

alter database add standby logfile thread 1 group 12 size 50m;

alter database add standby logfile thread 1 group 13 size 50m;

alter database flashback on;

2.2.5 修改主库参数

#ORCLCDB是主库的DB_UNIQUE_NAME，ORCLDG是备库的DB_UNIQUE_NAME

select DB_UNIQUE_NAME from v$database;

show parameter domain

 

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLCDB,ORCLDG)';

show parameter log_archive_config

 

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLCDB';

show parameter log_archive_dest_1

 

alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCLDG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDG';

 

alter system set LOG_ARCHIVE_DEST_2='service=ORCLDG LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0  reopen=30 net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ORCLDG';

 

show parameter log_archive_dest_2

 

#alter system set FAL_CLIENT=ORCLCDB;

alter system set FAL_SERVER=ORCLDG;

show parameter fal

alter system set DB_FILE_NAME_CONVERT='ORCLDG','ORCLCDB' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='ORCLDG','ORCLCDB' scope=spfile;

show parameter name_convert

 

alter system set STANDBY_FILE_MANAGEMENT=AUTO;

show parameter STANDBY_FILE_MANAGEMENT

 

alter system set db_create_file_dest='/opt/oracle/oradata';

show parameter db_create_file_dest

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

show parameter log_archive_format

2.2.6 添加到主备库的TNS连接

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

ORCLDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.101)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCLDG)

    )

  )

ORCLCDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.100)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCLCDB)

    )

  )

 

3.0 搭建备数据库Data Guard

3.1 OS节点准备

3.1.1 安装备库节点

#首先用1.0步骤同样的方法再安装一台Linux节点

##过程略

#测试环境关闭防火墙及selinux

systemctl stop firewalld

systemctl disable firewalld

systemctl status firewalld

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

setenforce 0

getenforce

 

3.1.2 oracle环境配置

yum install -y https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-3.el7.x86_64.rpm

#安装Oracle软件

yum -y install oracle-database-ee-19c-1.0-1.x86_64.rpm

#/etc/init.d/oracledb_ORCLCDB-19c configure

 

#配置用户环境变量，内容应与前面的配置文件相匹配(oracledb_ORCLCDB-19c)

su – oracle

vi .bash_profile

export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

export ORACLE_SID=ORCLDG

export PATH=$ORACLE_HOME/bin:$PATH

 

3.1.3 创建DB路径

cd /opt/oracle

mkdir -p admin/ORCLDG/adump

mkdir -p oradata/ORCLDG

mkdir -p fast_recovery_area/ORCLDG

 

3.1.4 拷贝网络文件

cd $ORACLE_HOME/network/admin

scp root@192.168.80.100:/opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora .

#拷贝密码文件

cd $ORACLE_HOME/dbs

scp root@192.168.80.100:/opt/oracle/product/19c/dbhome_1/dbs/orapwORCLCDB ./orapwORCLDG

 

vi tnsnames.ora

ORCLCDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.100)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCLCDB)

    )

  )

 

ORCLDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.101)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCLDG)

    )

  )

3.1.5 拷贝参数文件

#先在主库上创建一下参数文件

create pfile='/tmp/initORCLDG.ora' from spfile;

scp /tmp/initORCLDG.ora oracle@192.168.80.101:$ORACLE_HOME/dbs

3.2 开始配置Data Guard

3.2.1 启动辅助实例

#修改从主库拷贝过来的参数文件，注意db_name没有变化

su – oracle

vi $ORACLE_HOME/dbs/initORCLDG.ora

*.audit_file_dest='/opt/oracle/admin/ORCLDG/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/opt/oracle/oradata/ORCLDG/control01.ctl','/opt/oracle/oradata/ORCLDG/control02.ctl'

*.db_block_size=8192

*.db_name='ORCLCDB'

*.db_unique_name='ORCLDG'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/opt/oracle/fast_recovery_area/ORCLDG'

*.diagnostic_dest='/opt/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=ORCLDGXDB)'

*.fal_server='ORCLCDB'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLDG'

*.log_archive_dest_2=' SERVICE=ORCLCDB ASYNC   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB'

*.enable_pluggable_database=true

*.memory_target=1507m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.ENABLED_PDBS_ON_STANDBY=ORCLPDB1

 

sqlplus / as sysdba

startup nomount

create spfile from pfile

shutdown

startup nomount

quit

lsnrctl start

3.2.2 复制主库

#在主库创建数据库备份，创建备份时必须要将主库重启为MOUNT状态

shutdown immediate

startup mount

backup format '/home/oracle/backup/root_%U' database  root plus archivelog;

backup format '/home/oracle/backup/pdb_%U' pluggable database ORCLPDB1 plus archivelog;

delete backupset;

---将备份拷贝到备库相同目录下

scp -rp /home/oracle/backup oracle@192.168.80.101:/home/oracle/

 

#在主库创建备库要用的控制文件

alter database create standby controlfile as '/tmp/controlstd.ctl';

#此时可以打开主库

alter database open;

---把主库的控制文件远程拷贝到备库控制文件路径的下面

scp -rp /tmp/controlstd.ctl oracle@192.168.80.101:/tmp

3.2.3 在备库恢复控制文件

rman target /

shutdown

startup nomount

RESTORE STANDBY CONTROLFILE FROM '/tmp/controlstd.ctl';

alter database mount;

3.2.4 在备库恢复主库数据

#开始组合恢复命令

#先查出要恢复的根和PDB的con_id

select con_id,name  from v$pdbs

   CON_ID NAME

---------- ----------------

   2 PDB$SEED

   3 ORCLPDB1

#得出ID分别为1和3（根的ID永远是1）

---批量生成根容器root(con_id=1)以及pdb容器orclpdb1(con_id=3)的路径修改命令

set pagesize 0

set linesize 200

select 'set newname for datafile '||file#||' to '''||replace(name,'ORCLCDB','ORCLDG')||''';' from v$datafile where con_id in (1,3);

set pagesize 0

set linesize 200

select 'set newname for tempfile '||file#||' to '''||replace(name,'ORCLCDB','ORCLDG')||''';' from v$tempfile where con_id in (1,3);

---语句批量生成跳过表空间

set pagesize 400

set linesize 400

select listagg('"'||b.name||'"'||':'||a.name||',')

from v$tablespace a,v$containers b

where a.con_id=b.con_id and b.name not in ('CDB$ROOT','ORCLPDB1');

#根据以上产生的结果组合最后进行恢复的命令：

run

{

set newname for datafile 1 to '/opt/oracle/oradata/ORCLDG/system01.dbf';                             

set newname for datafile 3 to '/opt/oracle/oradata/ORCLDG/sysaux01.dbf';                             

set newname for datafile 4 to '/opt/oracle/oradata/ORCLDG/undotbs01.dbf';                            

set newname for datafile 7 to '/opt/oracle/oradata/ORCLDG/users01.dbf';                              

set newname for datafile 9 to '/opt/oracle/oradata/ORCLDG/ORCLPDB1/system01.dbf';                    

set newname for datafile 10 to '/opt/oracle/oradata/ORCLDG/ORCLPDB1/sysaux01.dbf';                   

set newname for datafile 11 to '/opt/oracle/oradata/ORCLDG/ORCLPDB1/undotbs01.dbf';                  

set newname for datafile 12 to '/opt/oracle/oradata/ORCLDG/ORCLPDB1/users01.dbf';       

set newname for tempfile 1 to '/opt/oracle/oradata/ORCLDG/temp01.dbf';                               

set newname for tempfile 3 to '/opt/oracle/oradata/ORCLDG/ORCLPDB1/temp01.dbf'; 

restore database root;

restore pluggable database ORCLPDB1;

switch datafile all;

switch tempfile all;

recover database skip forever tablespace "PDB$SEED":SYSTEM,"PDB$SEED":SYSAUX,"PDB$SEED":TEMP,"PDB$SEED":UNDOTBS1;

}

#最后在RMAN中执行以上命令，即可完成恢复

#完成恢复后打开备数据库

alter database open;

#禁用其他容器(除了root,pdb1)的恢复

alter session set container=PDB$SEED;

alter pluggable database PDB$SEED disable recovery;

#打开容器orclpdb1

alter pluggable database orclpdb1 open;

alter session set container=CDB$ROOT

alter database recover managed standby database using current logfile disconnect from session parallel 8;

#检查备库同步

select * from v$dataguard_stats;

 

dg
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论