3 、备份一体机在备库全量 + 增量恢复最近时间点

-- 查看备份一体机恢复mount 状态的SCN 点

SQL> select min(CHECKPOINT_CHANGE#)from v$datafile_header;

4 、备库配置

4.1 修改替换参数文件

vi /u01/app/oracle/product/19c/db/dbs/initorcl.ora

*._datafile_write_errors_crash_instance=FALSE

*._external_scn_logging_threshold_seconds=1800

*._external_scn_rejection_threshold_hours=240

*._kks_obsolete_dump_threshold=0

*._lm_sync_timeout=1200

*._lm_tickets=5000

*._optimizer_dsdir_usage_control=0

*._optimizer_extended_cursor_sharing='NONE'

*._optimizer_extended_cursor_sharing_rel='NONE'

*._optimizer_use_feedback=FALSE

*._part_access_version_by_number=FALSE

*._PX_use_large_pool=TRUE

*._serial_direct_read='NEVER'

*._SQL_plan_directive_mgmt_control=0

*._use_adaptive_log_file_sync='FALSE'

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_file_record_keep_time=38

*.control_files='+DATA01/ZBORCL/control01.ctl','+OCR_VOTE/ZBORCL/control02.ctl'

*.db_block_size=16384

*.db_cache_size=161061273600

*.db_files=5000

*.db_name='orcl'

*.deferred_segment_creation=FALSE

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers=''

*.enable_goldengate_replication=TRUE

*.large_pool_size=268435456

*.log_archive_dest_1='LOCATION=+ARCH'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=5000

*.pga_aggregate_target=81920m

*.processes=8000

*.recyclebin='OFF'

*.resource_manager_plan='default_plan'

*.sga_target=307200m

*.shared_pool_size=34359738368

*.streams_pool_size=2147483648

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.standby_file_management=auto

*.log_archive_config="DG_CONFIG=(orcl,zborcl)"

*.log_archive_dest_2='SERVICE=orcl1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl COMPRESSION=enable'

*.fal_server='orcl1'

*.fal_client='zborcl'

*.db_unique_name=zborcl  

4.2 创建参数文件相关目录

SHELL> mkdir /u01/app/oracle/admin/orcl/adump -p  

4.3 创建spfile

SQL> startup nomount pfile='/u01/app/oracle/product/19c/db/dbs/initorcl.ora'

SQL> create spfile from pfile='/u01/app/oracle/product/19c/db/dbs/initorcl.ora'  

4.4 在备库节点1注册服务

SHELL> su - oracle

SHELL> srvctl add database -db zborcl -dbname orcl -oraclehome $ORACLE_HOME -r physical_standby -startoption OPEN -stopoption IMMEDIATE  -spfile /u01/app/oracle/product/19c/db/dbs/spfileorcl.ora -pwfile /u01/app/oracle/product/19c/db/dbs/orapworcl

SHELL> srvctl config database -d zborcl  

4.5 spfile 启动nomount

SQL> shutdown immediate

SQL> startup  nomount

SQL> show parameter spfile  

4.6 主库基于SCN增量备份并创建控制文件

SHELL> vi /home/oracle/RMAN_inc.sh

#!/bin/bash

source ~/.bash_profile

RMAN target / log=/home/oracle/RMAN_inc.out <<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;

allocate channel c9 type disk;

allocate channel c10 type disk;

allocate channel c11 type disk;

allocate channel c12 type disk;

allocate channel c13 type disk;

allocate channel c14 type disk;

allocate channel c15 type disk;

allocate channel c16 type disk;

backup incremental from scn 25273833232 database format '/backup/inc_%U';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

release channel c7;

release channel c8;

release channel c9;

release channel c10;

release channel c11;

release channel c12;

release channel c13;

release channel c14;

release channel c15;

release channel c16;

}

alter database create standby controlfile as '/backup/control01.ctl';

exit

EOF

-- 查看增量备份进度

SQL> SELECT inst_id,

sid,

serial#,

opname,

ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"

FROM gV$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK;

-- 增量备份大，增量SCN 之后添加过数据文件，单独备份数据文件，减少恢复时间

SQL> select file#,name from v$datafile where creation_change#> =25273833232;

RMAN> backup datafile n format '/backup/n_%U.bak';  

4.7 备库替换控制文件启动到mount

RMAN> restore controlfile from '/tmp/control01.ctl';

SQL> startup mount;  

4.8 查看文件,若数据文件名存在MUST_RENAME或控制文件记录的数据文件名和asm内的数据文件真实名不一致则执行switch,临时文件rename,redo重建

-- 数据文件

情况1 ：

SQL> select name from v$datafile;

+DATA01/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295

+DATA01/ORCL/tb2_01.dbf

+DATA01/MUST_RENAME_THIS_DATAFILE_3.4294967295.4294967295

+DATA01/MUST_RENAME_THIS_DATAFILE_4.4294967295.4294967295

+DATA01/MUST_RENAME_THIS_DATAFILE_5.4294967295.4294967295

+DATA01/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295

情况2 ：

控制文件记录的数据文件名和asm 内的数据文件真实名不一致

情况1 解决：

RMAN> catalog start with '+DATA01';  -- 注册所有的数据盘

RMAN> select 'switch datafile '||file#||' to copy;'from v$datafile where name like '%MUST_RENAME%';

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

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

           2165134

                  0

           2165135

           2165135

           2165135

           2165134

ASMCMD> cp tb2_01.dbf ../         -- 创建链接保持和控制文件中的数据文件路径一致, 否则查询的checkpoint 为0

情况2 解决：

RMAN> catalog start with '+DATA01';  -- 注册所有的数据盘

RMAN> select 'switch datafile '||file#||' to copy;'from v$datafile;

-- 临时文件

SQL>

set pagesize 999

set linesize 999

select 'alter database rename file '||''''||name||''''||' to '||''''||'/data/'||substr(name,instr(name,'/',-1)+1)||'''' from v$tempfile;

--redo 或standby_redo 重建

SQL> select 'alter database drop logfile group '||GROUP#||';' from v$logfile;

SQL> alter database add standby logfile thread 1 '+STANDBY_LOG/ZBORCL/standby01.log' size 8192m;  

4.9 主库执行如下检查传输连通性传输归档

SQL> ALTER SYSTEM set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';

SQL> select dest_id,status,gap_status,error from v$archive_dest_status where dest_id = 2;  

4.10 备库清理控制文件无效备份信息

RMAN > crosscheck backup;

RMAN > delete noprompt expired backup;

RMAN > crosscheck archivelog all;

RMAN > delete noprompt expired archivelog all;  

4.11 增量恢复数据

RMAN>catalog start with '/backup/';

-- 确定主库在此scn 后是否添加过数据文件, 如果有增加过数据文件，执行这步，没有跳到下一步

SQL> select file#,name from v$datafile where creation_change#> =25273833232;

RMAN >restore datafile n;

SHELL> vi /home/oracle/RMAN_inc_recover.sh

#!/bin/bash

source ~/.bash_profile

RMAN target / log=/home/oracle/RMAN_inc_recover.out <<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;

allocate channel c9 type disk;

allocate channel c10 type disk;

allocate channel c11 type disk;

allocate channel c12 type disk;

allocate channel c13 type disk;

allocate channel c14 type disk;

allocate channel c15 type disk;

allocate channel c16 type disk;

recover database noredo;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

release channel c5;

release channel c6;

release channel c7;

release channel c8;

release channel c9;

release channel c10;

release channel c11;

release channel c12;

release channel c13;

release channel c14;

release channel c15;

release channel c16;

}

exit

EOF  

4.12 开启应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select process,status,sequence#,thread# from v$managed_standby;  

4.13 主备同步检查

SQL> select process,status,sequence#,thread# from v$managed_standby;

SQL> select dest_id,status,gap_status,error from v$archive_dest_status where dest_id = 2;

SQL> select name,value from v$dataguard_stats where name in ('transport lag','apply lag');