实战：YYYY最终版DG搭建灾备（服务器：10.10.10.10-9.9.9.9）

[oracle@i-670sg2lb ~]$ free -m
              total        used        free      shared  buff/cache   available
Mem:          32010       17238        3224         119       11546        8581
Swap:         32767           3       32764

[oracle@i-670sg2lb ~]$ df -h
Filesystem                         Size  Used Avail Use% Mounted on
devtmpfs                            16G     0   16G   0% /dev
tmpfs                               16G     0   16G   0% /dev/shm
tmpfs                               16G  121M   16G   1% /run
tmpfs                               16G     0   16G   0% /sys/fs/cgroup
/dev/vda3                           39G  8.4G   31G  22% /
/dev/vda1                          497M  335M  162M  68% /boot
tmpfs                              3.2G     0  3.2G   0% /run/user/0
/dev/mapper/VolGroup00-lv_u01       30G   17G   14G  56% /u01
/dev/mapper/VolGroup00-lv_oradata  500G  4.6G  496G   1% /oradata
tmpfs                              3.2G     0  3.2G   0% /run/user/99
tmpfs                              3.2G     0  3.2G   0% /run/user/1000
[oracle@i-670sg2lb ~]$

1.
--主库运行，查看归档模式是否打开
archive log list
--源端查看强制归档参数是否打开
select force_logging from v$database;
--源端打开强制归档
alter database force logging;

2.--源端备份数据库，备份前要检查目标路径空间是否足够
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;
crosscheck backup ;
crosscheck archivelog all;
crosscheck backupset ;
delete noprompt obsolete device type disk;
delete noprompt expired backup device type disk;
backup as compressed backupset full database format '/oradata/yangzhuangzhuang/full_%d_%t_%s_%p.bkp';
backup current controlfile format '/oradata/yangzhuangzhuang/cf_%d_%t_%s_%p.bkp';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}

3.--主库编辑pfile内容 从库复制内容
vi initYYYYY.ora
YYYYY.__data_transfer_cache_size=0
YYYYY.__db_cache_size=11408506880
YYYYY.__inmemory_ext_roarea=0
YYYYY.__inmemory_ext_rwarea=0
YYYYY.__java_pool_size=0
YYYYY.__large_pool_size=100663296
YYYYY.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
YYYYY.__pga_aggregate_target=3388997632
YYYYY.__sga_target=13455327232
YYYYY.__shared_io_pool_size=134217728
YYYYY.__shared_pool_size=1778384896
YYYYY.__streams_pool_size=0
YYYYY.__unified_pga_pool_size=0
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._serial_direct_read='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/YYYYY/adump'
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.compatible='19.0.0'
*.control_files='/oradata/yangzhuangzhuang/control01.ctl','/oradata/flash_recovery_area/yangzhuangzhuang/control02.ctl'
*.db_block_size=8192
*.db_name='YYYYY'
*.db_unique_name=YYYYYBAK
*.db_recovery_file_dest='/oradata/flash_recovery_area'
*.db_recovery_file_dest_size=8256m
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=YYYYYXDB)'
*.enable_ddl_logging=TRUE
*.event='10949 trace name context forever, level 1'
#*.local_listener='LISTENER_YYYYY'
*.log_archive_dest_1='LOCATION=/oradata/arch'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.parallel_degree_policy='MANUAL'
*.parallel_force_local=TRUE
*.parallel_max_servers=64
*.parallel_min_servers=2
*.pga_aggregate_target=3201m
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=0
*.sga_target=12804m
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'

--备库执行，修改pfile文件内如下参数
--SGA和pga之和不能超过当前主机内存的一半
mkdir -p /u01/app/oracle/admin/yangzhuangzhuang/adump

*.control_files='/oradata/yangzhuangzhuang/controlfile/o1_mf_j02sw15c_.ctl'
*.pga_aggregate_target=4G
*.sga_target=8G
*.db_unique_name=YYYYYBAK
*.audit_file_dest='/u01/app/oracle/admin/YYYYY/adump'

4.主库scp密码文件和备份文件
--scp  10.9.9.15:/oradata/rman/yqb/* . 

scp /oradata/yangzhuangzhuang/* oracle@9.9.9.9:/oradata/yangzhuangzhuang/
scp $ORACLE_HOME/dbs/orapwYYYYY oracle@9.9.9.9:$ORACLE_HOME/dbs/

5.启动到nomount 恢复standby控制文件
--备库执行，使用修改过后的pfile启动数据库
sqlplus / as sysdba
startup nomount

--备库执行
run {
allocate channel c1 type disk; 
restore standby controlfile from '/oradata/yangzhuangzhuang/cf_YYYYY_1091458326_5_1.bkp';
release channel c1;
}

6.启动到Mount 
--在备库操作，rman提示符下操作
--备库操作，sqlplus提示符
alter database mount;
alter system set standby_file_management='manual';

--备库操作
set linesize 200
col member for a50
col status for a10
select GROUP#,bytes/1024/1024 MByte from v$log;
select * from v$logfile;

7.增加standby日志组
alter database rename file '/oradata/yangzhuangzhuang/redo04.log' to '/oradata/YYYYY/redo01a.log';
alter database rename file '/oradata/yangzhuangzhuang/redo05.log' to '/oradata/YYYYY/redo02a.log';
alter database rename file '/oradata/yangzhuangzhuang/redo06.log' to '/oradata/YYYYY/redo03a.log';

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

alter database add standby logfile group 101 '/oradata/yangzhuangzhuang/standbyredo1.log' size 200m;
alter database add standby logfile group 102 '/oradata/yangzhuangzhuang/standbyredo2.log' size 200m;
alter database add standby logfile group 103 '/oradata/yangzhuangzhuang/standbyredo3.log' size 200m;
alter database add standby logfile group 104 '/oradata/yangzhuangzhuang/standbyredo4.log' size 200m;

*——alter database add logfile group 3 '/oradata/yangzhuangzhuang/redo03.log' size 200m;

--8.查看DG状态
set line 500
col gap_status for a30
col type for a20
select dest_id,status,type,database_mode,gap_status,error from v$archive_dest_status where dest_id=2;

col value for a20
col time_computed for a30
col name for a25
col unit for a30
col datum_time for a30
set line 400
select * from v$dataguard_stats;

9.备库restore数据文件
--备库操作，rman提示符
--注册文件
catalog start with '/oradata/yangzhuangzhuang';

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

recover database;

10.--检查监听
lsnrctl status
--没有启动要启起来
lsnrctl start

11.配置TNS

YYYYY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = YYYYY)
    )
  )

YYYYYBAK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9.9.9.9)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = YYYYYBAK)
    )
  )
  
--验证监听
--主库
tnsping YYYYYBAK
tnsping YYYYY
--备库
tnsping YYYYYBAK
tnsping YYYYY

12.创建spfile并重启
--备库执行，sqlplus提示符，生产spfile
create spfile from pfile;
--备库执行，sqlplus提示符，重启数据库
shutdown immediate;
startup mount;

13.修改DG相关参数
--主库执行
alter system set fal_server='YYYYYBAK' sid='*'scope=both;
alter system set fal_client='YYYYY' sid='*' scope=both; 
alter system set log_archive_config='dg_config=(YYYYY,YYYYYBAK)' sid='*' scope=both;

--确认主库的归档路径序列，如果已经存在，依次递增
show parameter log_archive_dest
alter system set log_archive_dest_2='service=YYYYYBAK lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=YYYYYBAK' sid='*' scope=both;
alter system set log_archive_dest_state_2='enable';

--备库执行
alter system set fal_server='YYYYY' sid='*'scope=both;
alter system set fal_client='YYYYYBAK' sid='*' scope=both; 
alter system set standby_file_management=auto sid='*' scope=both;
alter system set log_archive_config='dg_config=(YYYYY,YYYYYBAK)' sid='*' scope=both;
alter system set log_archive_dest_1='location=/oradata/arch' sid='*' scope=both;
alter system set log_archive_dest_2='service=YYYYY lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=YYYYY' sid='*' scope=both;
alter system set log_archive_dest_state_2='enable';

14.开始同步 无延迟后开库
alter database recover managed standby database disconnect from session using current logfile;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database disconnect from session using current logfile;

15.创建check.sh 查看DG状态
echo '##################################  YYYYY  #######################'
export ORACLE_SID=YYYYY
sqlplus -s /nolog <<EOF
connect / as sysdba
col VALUE for a20
col TIME_COMPUTED for a30
col name for a25
col UNIT for a30
col DATUM_TIME for a30
col OPEN_MODE for a30
col DATABASE_ROLE for a30
set line 400
select name,value,datum_time from v\$dataguard_stats where name in ('transport lag','apply lag');
select open_mode , database_role from v\$database;
exit;
EOF
df -h|egrep -e "Filesystem|arch|oradata"

16.创建测试表 查看是否同步
create table yangzhuangzhuang_ghq.test0118 as select * from dba_objects;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
desc yangzhuangzhuang_ghq.test0118 

PS:以下内容为参考，非必要操作
--备库alert
[RFS]
--传输归档
Media
1.--归档应用
alter system set log_archive_dest_1='location=/oradata/arch/yangzhuangzhuang';

2.--开启闪回
show parameter db_recovery
alter system set db_recovery_file_dest_size=100g;
alter system set db_recovery_file_dest='/oradata';
alter database recover managed standby database cancel;

3.alter database flashback on;
--查询是否开启
select flashback_on from v$database;
alter database recover managed standby database disconnect from session using current logfile;

4.--在备库alert
GAP指的就是归档缺失，伴随着sequence区间
alter system set log_archive_dest_state_3='defer' scope=memory;
alter system set log_archive_dest_state_3='enable' scope=memory;

5.--删除最近三天归档
--find /oradata/arch/eva -mtime +3 -exec rm -f {} \;

6.--导入导出
expdp \'/ as sysdba \' directory=dump_dir dumpfile=yangzhuangzhuang_20220529_%U.dmp logfile=yangzhuangzhuang_20220529.log schemas=A,B,C,D,E,F parallel=8

impdp \'/ as sysdba \' directory=dump_dir dumpfile=yangzhuangzhuang_20220529_%U.dmp logfile=yangzhuangzhuang_20220529.log full=y parallel=8

7.--备份增量脚本,scn使用如下脚本查询,取最小的SCN
set linesize 400
col name for a70
col CHECKPOINT_CHANGE# for 99999999999999999999
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select file#,status,name,CHECKPOINT_TIME, CHECKPOINT_CHANGE# from v$datafile_header order by CHECKPOINT_TIME;

8.--备份指定scn号
run {
backup as compressed backupset incremental from scn 104797379 database format '/oradata/rman/incr_%d_%T_%s_%p.bkp';
}

backup as compressed backupset INCREMENTAL from scn 1418731623 database format '/oradata/rman/incremental_%d_%t_%s_%p.BAK';

backup as compressed backupset full filesperset 1 database format '/oradata/rman/full_%d_%t_%s_%p' plus archivelog format '/oradata/rman/%d_ar_%t_%s.bkp';

add ext extu1, integrated tranlog,begin now
add exttrail /goldengate/dirdat/u1, extract extu1, megabytes 100

alter ext extu1 scn 2602622057

add ext dpeu1,exttrailsource /goldengate/dirdat/u1
add rmttrail /goldengate/dirdat/r1, ext dpeu1 ,megabytes 100

add rep rep01 ,exttrail /goldengate/dirdat/r1 checkpointtable goldengate.ckpt

9.--开启关闭standby_file_management
alter system set standby_file_management='auto';
alter system set standby_file_management='manual';

10.--备份执行数据文件3
backup datafile 3 format '/oradata/rman/file_%d_%t_%s_%p.bkp';
alter user yangzhuangzhuang identified by "yangzhuangzhuang123!";

11.--修改数据文件位置
col name for a100
select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/oradata/yangzhuangzhuangBAK/datafile/o1_mf_system_j089rovn_.dbf
/oradata/yangzhuangzhuangBAK/datafile/o1_mf_sysaux_j089rnt7_.dbf
/oradata/yangzhuangzhuangBAK/datafile/o1_mf_undotbs1_j089rnst_.dbf
/oradata/yangzhuangzhuangBAK/datafile/o1_mf_users_j089rnsw_.dbf
/oradata/yangzhuangzhuang/HSAPI01.dbf

cp /oradata/yangzhuangzhuangBAK/datafile/o1_mf_system_j089rovn_.dbf /oradata/yangzhuangzhuang/system01.dbf
cp /oradata/yangzhuangzhuangBAK/datafile/o1_mf_sysaux_j089rnt7_.dbf /oradata/yangzhuangzhuang/sysaux01.dbf
cp /oradata/yangzhuangzhuangBAK/datafile/o1_mf_undotbs1_j089rnst_.dbf /oradata/yangzhuangzhuang/undotbs01.dbf
cp /oradata/yangzhuangzhuangBAK/datafile/o1_mf_users_j089rnsw_.dbf /oradata/yangzhuangzhuang/user01.dbf

alter database rename file '/oradata/yangzhuangzhuangBAK/datafile/o1_mf_system_j089rovn_.dbf' to '/oradata/yangzhuangzhuang/system01.dbf';
alter database rename file '/oradata/yangzhuangzhuangBAK/datafile/o1_mf_sysaux_j089rnt7_.dbf' to '/oradata/yangzhuangzhuang/sysaux01.dbf';
alter database rename file '/oradata/yangzhuangzhuangBAK/datafile/o1_mf_undotbs1_j089rnst_.dbf' to '/oradata/yangzhuangzhuang/undotbs01.dbf';
alter database rename file '/oradata/yangzhuangzhuangBAK/datafile/o1_mf_users_j089rnsw_.dbf' to '/oradata/yangzhuangzhuang/user01.dbf';

重新注册再restore