Oracle 19.3 dataguard for Redhat 7.6

最新推荐文章于 2024-09-20 14:02:53 发布

ii_木兮

最新推荐文章于 2024-09-20 14:02:53 发布

阅读量334

点赞数 1

分类专栏： 19c 安装部署&系统建设

本文链接：https://blog.csdn.net/weixin_37570923/article/details/107403207

版权

安装部署&系统建设同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

19c

2 篇文章 0 订阅

订阅专栏

文档内容

在redhat 7.6环境下部署19c dataguard

环境信息

部署步骤

1、检查并开启force_logging

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

2、查看各PDB数据量：

select a.con_id,b.name,round(sum(bytes/1024/1024/1024)) sum_gb from cdb_segments a,v$pdbs b where a.con_id=b.con_id group by a.con_id,b.name;

CON_ID NAME                     SUM_GB
------ -------------------- ----------
       PDB$SEED
     3 pdb01                      785
     4 pdb02                       508
     5 pdb03                       107

3、按cdb、pdb进行0级别备份
注：此处由于数据量问题及个人测试想法采用cdb root、pdb分开备份恢复，可根据需求决定是否采用全库备份

cat /home/oracle/scripts/rman_incr0_for_dg_backup.sh
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / log=/home/oracle/rman_incr0_for_dg_`date +%Y%m%d`.log <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;

BACKUP  as compressed backupset incremental level 0  DATABASE root 
tag hot_incr0_root_bk_level0
format '/backup/rman/cdbtest_root_incr0_%d_%T_%s_%p_%t';

BACKUP  as compressed backupset incremental level 0 PLUGGABLE DATABASE pdb01,pdb02,pdb03,"pdb\$seed"
tag hot_incr1_pdb_bk_level0
format '/backup/rman/cdbtest_pdb_incr0_%d_%T_%s_%p_%t';

backup current controlfile for standby
tag hot_ctl_bk 
format '/backup/rman/cdbtest_ctl_%d_%T_%s_%p_%t';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
} 
EOF

4、传输备份集、口令文件、初始化参数文件、配置tnsnames.ora文件

4.1 传输备份集

4.2 传输口令文件(RAC中口令文件存放在ASM)

srvctl config database -d cdbtest
Database unique name: cdbtest
Database name: cdbtest
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA_DG/cdbtest/PARAMETERFILE/spfile.267.1018633335
Password file: +DATA_DG/cdbtest/PASSWORD/pwdcdbtest.256.1018632371
cp +DATA_DG/cdbtest/PASSWORD/pwdcdbtest.256.1018632371 /backup/rman/orapwcdbtest

4.3 配置tnsnames.ora文件

##主备库各节点
cdbtest  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.12.80)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbtest)
    )
  )
cdbteststd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.16.80)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdbteststd)
    )
  )

sqlplus sys/"password"@cdbtest as sysdba
sqlplus sys/"password"@cdbteststd as sysdba

4.4 创建并传输备库初始化话参数文件

##主库
create pfile='/tmp/standby.ora' from spfile;
scp /tmp/standby.ora /backup/rman/

##备库
scp /backup/rman/standby.ora  /u01/app/oracle/product/19.0.0/dbhome_1/dbs/initcdbteststd.ora
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initcdbtest.ora
*._disable_file_resize_logging=TRUE
*.audit_file_dest='/u01/app/oracle/admin/cdbteststd/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/data/oradata/controlfile/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/data'
*.db_create_online_log_dest_1='/data'
*.db_name='cdbtest'
*.db_recovery_file_dest_size=1000g
*.db_recovery_file_dest='/data02'
*.db_unique_name='cdbteststd'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdbtestXDB)'
*.enable_pluggable_database=true
*.enabled_PDBs_on_standby='pdb01','pdb02','pdb03'    ##该参数可根据具体同步pdb进行配置
*.fal_client='cdbteststd'
*.fal_server='cdbtest'
family:dw_helper.instance_mode='read-only'
*.log_archive_config='DG_CONFIG=(cdbteststd,cdbtest)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=2000
*.pga_aggregate_target=3G
*.processes=2560
*.remote_login_passwordfile='exclusive'
*.sga_target=9G
*.undo_tablespace='UNDOTBS1'

5、备库0级恢复

5.1 启动到nomount状态

create spfile from pfile;
startup nomount

5.2 恢复控制文件

rman target /
restore controlfile from '/backup/rman/cdbtest_ctl_cdbtest_202006_76_1_1037976677'
alter database mount;

5.3 恢复0级备份

show all;
configure device type disk parallelism 16;
restore database root;
restore pluggable database pdb01,pdb02,pdb03,"pdb$seed";

6、主库1级备份

cat rman_incr1_for_dg_backup.sh 
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman target / log=/home/oracle/rman_cdbtest.log <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;

BACKUP  as compressed backupset incremental level 1 database root
tag hot_incr1_root_bk_level1
format '/backup/rman/cdbtest_root_incr1_%d_%T_%s_%p_%t';

BACKUP  as compressed backupset incremental level 1 PLUGGABLE DATABASE pdb01,pdb02,pdb03,"pdb\$seed"
tag hot_incr1_pdb_bk_level1
format '/backup/rman/cdbtest_pdb_incr1_%d_%T_%s_%p_%t';

backup current controlfile for standby
tag hot_ctl_bk 
format '/backup/rman/cdbtest_ctl_%d_%T_%s_%p_%t';

release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
} 
EOF

7、备库1级恢复

catalog start with '/backup/rman';
recover database root;
recover pluggable database pdb01,pdb02,pdb03,"pdb\$seed";

shutdown immediate
startup nomount
restore controlfile from '/backup/rman/cdbtest_ctl_cdbtest_202006_116_1_1037982709';
alter database mount;

catalog start with '/data/oradata';
switch database root to copy;
switch pluggable database pdb01,pdb02,pdb03,"pdb\$seed" to copy;

8、配置主备库dataguard同步参数

8.1 主库参数配置

create pfile='/home/oracle/pfile.bak' from spfile;
alter system set log_archive_config='DG_CONFIG=(cdbtest,cdbteststd)' sid='*' scope=both;
alter system set log_archive_dest_10='service=cdbteststd db_unique_name=cdbteststd reopen=30 compression=enable valid_for=(all_logfiles, primary_role)' scope=both sid='*';
alter system set log_archive_dest_state_10=enable sid='*' scope=both;
alter system set standby_file_management=auto sid='*' scope=both;
alter system set fal_server='cdbteststd' sid='*' scope=both;
alter system set fal_client='cdbtest' sid='*' scope=both;

8.2 主库参数配置

alter system set log_archive_config='DG_CONFIG=(cdbteststd,cdbtest)' sid='*' scope=both;
alter system set fal_server='cdbtest' sid='*' scope=both;
alter system set fal_client='cdbteststd' sid='*' scope=both;

9、备库添加standby log、启动日志同步

##添加standbt log
alter database add standby logfile group 101 size 1073741824;
alter database add standby logfile group 102 size 1073741824;
alter database add standby logfile group 103 size 1073741824;
alter database add standby logfile group 104 size 1073741824;
alter database add standby logfile group 105 size 1073741824;
alter database add standby logfile group 106 size 1073741824;

##启动日志同步
alter database recover managed standby database disconnect from session parallel 16;
alter database recover managed standby database cancel;
alterd database open read only;
alter database recover managed standby database disconnect from session parallel 16;

10、主备同步检查

##主库检查：
select error,status from gv$archive_dest where dest_id=10;
select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

##备库检查：
select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;
select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

##日志同步检查
set line 200
select process,sequence#,status,thread# from v$managed_standby;
set line 200
col  NAME           for a20                                                                                                    
col  VALUE          for a20                                                                                                    
col  UNIT           for a30                                                                                                    
col  TIME_COMPUTED  for a20                                                                                                    
col  DATUM_TIME     for a20
select * from v$dataguard_stats;
archive log list