环境:11.2.0.4 ,主库双节点rac+asm,备库单节点文件系统
数据恢复方式:duplicate或者rman
备库先搭好环境,不用建库。
尽量保持主备库psu版本一致,
(以上步骤略)
准备阶段
1.ntp时间同步
#date
#设置主备库的ntp时间同步
client:
server xx.xx.xx.147 prefer
driftfile /var/lib/ntp/drift
broadcastdelay 0.008
server:
server xx.xx.xx.0
fudge xx.xx.xx.0 stratum 11
driftfile /var/lib/ntp/drift
broadcatdelay 0.008
#手动同步
ntpdate xx.xx.xx.147
2.归档和force logging
#主库开启归档和force logging
#archive
archive log list
#force logging
select name,log_mode,FORCE_LOGGING from v$database;
alter database force logging;
3.standby log
#standby logs要比redo logs多一组
select INST_ID,group#,thread#,bytes/1024/1024 mb,members,status from gv$log order by 3,2;
select INST_ID,group#,thread#,bytes/1024/1024 mb,status from gv$standby_log order by 3,2;
alter database add standby logfile thread 1 group 13 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 14 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 15 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 16 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 17 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 18 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 19 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 20 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 21 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 22 '+DATA' size 2048m;
4.主库参数设置
alter system set db_block_checking=FULL scope=both sid='*';
alter system set db_block_checksum=FULL scope=both sid='*';
alter system set db_lost_write_protect=TYPICAL scope=both sid='*';
alter system set log_archive_config='DG_CONFIG=(lzldb,lzldbadg)' scope=both sid='*';
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lzldb' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=lzldbadg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lzldbadg' scope=both sid='*';
alter system set fal_server=lzldbadg scope=both sid='*';
alter system set fal_client=lzldb scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
5.备库的静态监听
#备库listener.ora
#必须是静态监听, 如果pfile启动时有service_name,那么会注册blocked类型的动态监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lzldbadg)
(ORACLE_HOME = /u01/oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = lzldb)
)
)
ADR_BASE_LISTENER = /u01/oracle/ap
lsnrctl stop
lsnrctl start
6.主备库的tns
#tnsnames.ora
#因为做了备库地址转换,主备tns略有不同
lzldb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.151 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzldb)
)
)
lzldbadg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.16 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzldbadg)
)
)
#stddb
lzldb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.151 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzldb)
)
)
lzldbadg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.16 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = lzldbadg)
)
)
7.密码文件和pfile
#orapwfile,主库最好也scp一下,保持多个节点的密码文件一致
scp $ORACLE_HOME/dbs/orapwlzldb1 xxx.xxx.xx.16:/home/oracle/orapwlzldb
cp ~/orapwlzldb $ORACLE_HOME/dbs
#pfile pwdfile
create pfile='/home/oracle/enmo/pfile.ora' from spfile;
#scp /home/oracle/enmo/pfile.ora xxx.xxx.xx.16:/home/oracle/pfile.ora
#备库打开拷贝过来的pfile,直接进行修改
#备库环境与主库不同,无grid,内存较小,base和home不同
lzldb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/oracle/app/admin/lzldb/adump'
*.audit_trail='NONE'
*.control_files='/u01/oracle/app/oradata/lzldb/control01.ctl' --standby controlfile的位置
*.db_create_file_dest='/u01/oracle/app/oradata/lzldb'
*.db_create_online_log_dest_1='/u01/oracle/app/oradata/lzldb'
*.db_name='lzldb'
*.db_unique_name='lzldbadg'
*.db_recovery_file_dest='/u01/oracle/fast_recovery'
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest='/u01/oracle/app'
*.fal_client='lzldbadg' --备库的fal参数与主库相反
*.fal_server='lzldb'
*.instance_number=1 --备库实例个数可能与主库不一样
*.job_queue_processes=300
*.log_archive_config='DG_CONFIG=(lzldb,lzldbadg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lzldbadg'
*.log_archive_dest_2='SERVICE=lzldb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lzldb'
*.db_file_name_convert='+DATA','/u01/oracle/app/oradata/lzldb','+DATA/lzldb/datafile','/u01/oracle/app/oradata/lzldb','+DATA/lzldb/tempfile','/u01/oracle/app/oradata/lzldb'
*.log_file_name_convert='+DATA/lzldb/onlinelog','/u01/oracle/app/oradata/lzldb'
*.open_cursors=2000 --备库的配置可能比主库低,所以内存等参数需要调整
*.pga_aggregate_target=10995367936
*.session_cached_cursors=400
*.sessions=3000
*.sga_max_size=27514634240
*.sga_target=27514634240
*.standby_file_management='AUTO'
*.thread=1
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
8.创建目录
#mkdir
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,pfile}
mkdir -p /u01/oracle/app/admin/lzldb/adump
mkdir -p /u01/oracle/app/admin/lzldb/dpdump
mkdir -p /u01/oracle/app/admin/lzldb/pfile
mkdir -p /u01/oracle/app/oradata/lzldb
9.启动备库到nomount
#startup nomount
SQL> startup nomount pfile='/home/oracle/pfile.ora'
SQL> create spfile from pfile='/home/oracle/pfile.ora';
SQL> !ls $ORACLE_HOME/dbs/spfile*
/u01/oracle/app/product/11.2.0/dbhome_1/dbs/spfilelzldb.ora
10.standby controlfile
#control file
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control01.ctl'
scp /home/oracle/control01.ctl xxx.xxx.xx.16:/u01/oracle/app/oradata/lzldb/control01.ctl
数据恢复阶段
11.拉数据
#2个办法,一个是duplicate一个数据库,还有就是rman备份恢复
#duplicate
#建议放后台执行
script /dev/null
screen -S rman_dup
screen -ls
screen -d -r rman_dup
#duplicate
rman target sys/Oracle1234@lzldb auxiliary sys/Oracle1234@lzldbadg nocatalog
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
allocate auxiliary channel aux4 device type disk;
SET NEWNAME FOR DATABASE TO new;
duplicate target database for standby from active database nofilenamecheck;
}
=========================================================================
#rman备份恢复
#准备nfs
vi /etc/exports
service nfs restart
mount /dev/mapper/data1p3 /xdump
showmount -e xxx.xxx.xx.148
mount -t nfs -o nolock xxx.xxx.xx.148:/xdump /xdump
chown oracle:oinstall /xdump
rman target /
#放后台执行
run {
#Hot database whole backup
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;
configure controlfile autobackup off;
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 database
Backup as compressed backupset database format '/xdump/db_cp_%U';
#delete obsolete;
######backup all archive logs
sql 'alter system archive log current';
backup as compressed backupset
skip inaccessible
filesperset 20
format '/xdump/arclogback_%s_%p_%t_%d'
archivelog all delete input;
#delete obsolete;
######backup controlfile
backup current controlfile format '/xdump/control_bak_%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;
}
#restore
#如果cp了standby controlfile就不需要restore 了
restore standby controlfile from '/xdump/control_bak_20181225';
RMAN> alter database mount;
catalog start with '/xdump/';
list backup of datafile 1;
#配置channel
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;
restore database;
Release channel c1;
Release channel c2;
Release channel c3;
Release channel c4;
Release channel c5;
Release channel c6;
Release channel c7;
Release channel c8;
}
#检查rman恢复日志,可能会遇到某些数据文件没有恢复的情况,可以使用下面的命令指定恢复数据文件
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;
restore datafile 117;
restore datafile 116;
restore datafile 112;
restore datafile 108;
restore datafile 104;
restore datafile 102;
restore datafile 101;
restore datafile 88 ;
restore datafile 85 ;
restore datafile 84 ;
restore datafile 83 ;
restore datafile 77 ;
restore datafile 76 ;
restore datafile 74 ;
restore datafile 71 ;
restore datafile 70 ;
restore datafile 67 ;
restore datafile 66 ;
restore datafile 63 ;
restore datafile 61 ;
restore datafile 55 ;
restore datafile 45 ;
restore datafile 44 ;
restore datafile 43 ;
restore datafile 42 ;
restore datafile 41 ;
restore datafile 40 ;
restore datafile 39 ;
restore datafile 38 ;
restore datafile 37 ;
restore datafile 35 ;
restore datafile 32 ;
restore datafile 28 ;
restore datafile 16 ;
restore datafile 9 ;
restore datafile 5 ;
Release channel c1;
Release channel c2;
Release channel c3;
Release channel c4;
Release channel c5;
Release channel c6;
Release channel c7;
Release channel c8;
}
#也可以指定数据文件的名字进行恢复
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/users_default.366.995324027';
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/sysaux.257.994159463';
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/system.256.994159463';
#recover
#rman不会备份redo,使用 using current logfile自动创建redo
#dg redo不可create redo手动创建,使用clear logfile可以直接创建logfile
recover和检查dg问题阶段
12.recover
alter database recover managed standby database using current logfile disconnect from session;
#or
alter database recover managed standby database disconnect;
#停日志应用alter database recover managed standby database cancel;
#如果仍有redo没有创建,需手动创建redo
alter database clear logfile group 13;
alter database clear logfile group 14;
alter database clear logfile group 15;
alter database clear logfile group 16;
alter database clear logfile group 21;
--查看归档应用情况,检查是否有gap,如有gap需要基于scn号做增量(主库也没有归档的情况下),或者追归档
col name for a100
select sequence#,applied,NAME from v$archived_log order by sequence#;
select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024 mb ,status from v$standby_log;
#检查
#检查进程情况
select process,status,client_process,thread#,sequence# from v$managed_standby;
--主库,查看archive dest是否有报错
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST ;
#手动全库归档
alter system archive log current;
#检查log dest是否有报错,2个节点都检查
select dest_id,error from v$archive_dest
#检查日志间隔
select thread#,low_sequence#,high_sequence# from v$archive_gap;
col name for a25
col value for a20
col unit for a30
set lines 120
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
#查看dg模式
select protection_mode,protection_level from v$database;
13.read only
# alter database flashback on;
alter database open read only;
问题一:修复gap
#这里遇到了主库归档丢失的情况,只能通过基于scn号增量备份的方式恢复dg
#http://blog.itpub.net/9240380/viewspace-1424363/
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
#std
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-------------
16170506639
#pri
mkdir -p /xdump/incre
rman target /
run
{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL FROM SCN 16170506639 database format '/xdump/incre/forstandby_%d_%T_%s_%p.bkp';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
#pri
alter database create standby controlfile as '/xdump/incre/standby.ctl';
#std
#chown -R oracle:oinstall /xdump/incre
startup nomount
#restore
rman target /
restore standby controlfile from '/xdump/incre/standby.ctl';
alter database mount;
#recover
catalog start with '/xdump/incre' noprompt;
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
recover database noredo;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
#dg
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;