分类： Oracle
2008-09-16 11:05:41
ORACLE9I+DATAGUARD+RMAN
第一、修改数据库为归档模式
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system archive log start;
第二、复制数据库
1.利用RMAN来复制数据库,步骤如下(一般不是文件系统才使用此方法).
******************RMAN备份用户需要的权限*****************
SQL>grant recovery_catalog_owner to rman;
SQL>grant connect, resource to rman;
***********************************************************
Rman nocatalog target sys/sys@ORACLE_SID
RMAN>Backup database;
RMAN> Backup current controlfile for standby;
RMAN> sql "Alter System Archive Log Current";
RMAN>exit;
2．操作系统的复制(一般适合文件系统)
第三、配置DATAGUARD
1. 创建备用数据库的参数和控制文件。
SQL>create pfile from spfile;
SQL>alter database create standby controlfile as ‘/path/filename’;
SQL> alter system archive log current;
2. 修改/添加参数文件.
Primary INIT.ORA setup:
==========================
db_name = PROD
instance_name = PROD
fal_client = PROD
fal_server= STANDBY
log_archive_dest_1 = 'LOCATION= /arch'
log_archive_dest_2 = 'SERVICE=STANDBY LGWR ASYNC AFFIRM'
log_archive_format = %t_%s.arc
log_archive_start = true
remote_login_passwordfile = EXCLUSIVE
service_names =PROD
Standby INIT.ORA setup:
==========================
# Copy the init.ora from production to standby.
# Edit the initSTANDBY.ora to make neccessary changes:
db_name = "PROD" -- Must match the production database.
instance_name = STANDBY -- Instance identifier
lock_name_space = STANDBY -- Used when the standby or clone have the same
name as the production database being copied.
service_names = STANDBY -- Specifies the service names supported by the
instance.
fal_client = STANDBY -- Specifies the service name resolved from the
remote host to fetch archive logs to local.
fal_server = PROD -- Specifies the service name resolved on the
local host to request archive logs from remote.
db_file_name_convert = ('/p01/oradata/PROD',
'/u03/STANDBY/oradata')
log_file_name_convert = ('/p01/oradata/PROD',
'/u03/STANDBY/oradata')
# Use these parameters if you have a different filesystem structure on the
# standby. Then edit all the dump_dest and controlfile locations for the
# standby site.
standby_archive_dest = /p01/app/oracle/product/9.0.1/dbs/arch
standby_file_management = auto
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
可以用以下语句来进行模式间的切换，默认是maximize performance。
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};
修改主数据库保护模式需要重启数据库才能生效。
select protection_mode,protection_level from v$database;
3. 配置网络连接
修改TNSNAMES.ORA文件。修改完毕后tnsping测试通过，否则日志传输会受到影响。
4.启动备用数据库
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session.
5.配置完毕后检查data-guard是否正常工作。
cd $ORACLE_BASE/admin/[SID]/bdump
tail –f alert_[SID].log如果看到如下信息说明data-guard已经正常运行。
…………………
Meadia Recovery Waitting from thread 1 seq#5
Completed:alter database recover managed standby database…
Media Recovery log /oracle/oradata/db/archive/1_5.arc
Media Recovery Waitting from thread 1 seq#6
4. 注意一下几种情况是不会产生日志
Direct path load
用子查询建立表
在主数据库上建立索引
表或索引的属性为nologging
如果主数据库的操作不产生日志，所做的修改就不会应用到备份数据库上，所以在建立备用数据库的时候，我们应该在主数据库上执行alter database force logging命令以强制生成日志。
5. 数据库角色的转换
Alter database commit to switchover
把主数据库转换成物理备用数据库
alter database commit to switchover to physical standby with session shutdown;
shutdown
startup nomount pfile=/dbs/inittbdbsdby.ora
alter database mount standby database;
recover managed standby database disconnect;
把备用数据库转换成主数据库
alter database commit to switchover to primary;
shutdown
startup pfile=/dbs/inittbdb.ora
1、如果在备用端有活动的未归档的日志，或者有从主数据库拷贝过来的联机日志，可以采
用如下的办法注册并恢复
SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oracle/oradata/tbdb/archive/1_87.dbf';
SQL>recover standby database;
2、如果有活动日志，必须用
alter database recover managed standby database finish;
否则用
alter database recover managed standby database finish skip standby logfile;
这样切换的备用服务器可以避免最小的数据丢失和不用resetlogs，特别是对于用多个备用服务器的时候，该服务器可以马上作为主服务器而不用重新创建备用服务器
主机rman nocatalog备份数据库。每个星期五21：00点开始全库备份，周一至周四备份归档日志。
配置策略：
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; (备份策略保持)
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS; (是保证至少有一个备份能恢复到Sysdate-5 的时间点上，之前的备份将标记为Obsolete)
CONFIGURE RETENTION POLICY TO REDUNDANCY 5; (至少需要有三个冗余的备份存在，如果多余三个备份以上的备份将标记为冗余)
CONFIGURE RETENTION POLICY TO NONE; (NONE 可以把使备份保持策略失效)
CONFIGURE RETENTION POLICY TO CLEAR; (Clear 将恢复默认的保持策略)
CONFIGURE BACKUP OPTIMIZATION OFF; (备份优化开关)
CONFIGURE DEFAULT DEVICE TYPE TO DISK; （配置默认IO 设备类型）
CONFIGURE CONTROLFILE AUTOBACKUP OFF; (自动备份控制文件开关)
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR TYPE DISK TO '%F'; (指定控制文件的备份路径与格式)
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; （并行备份，同时开启1个通道进行备份与恢复）
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; (多个备份的拷贝数目)
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; (多个归档备份的拷贝数目)
CONFIGURE MAXSETSIZE TO UNLIMITED; (备份集大小限制)
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.0.2/dbs/snapcf_U02.f'; (在备份期间，将产生一个控制文件的快照，用于控制文件的读一致性)
################################################
# My configure
################################################
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR TYPE DISK TO '/BACKUP/Control_%d_%T';
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE MAXSETSIZE TO 2G;
###############################################
# %c 备份片的拷贝数
# %d 数据库名称
# %D 位于该月中的第几天 (DD)
# %M 位于该年中的第几月 (MM)
# %F 一个基于DBID 唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,
# 其中IIIIIIIIII 为该数据库的DBID，YYYYMMDD 为日期，QQ 是一个1-256 的序
# 列
# %n 数据库名称，向右填补到最大八个字符
# %u 一个八个字符的名称代表备份集与创建时间
# %p 该备份集中的备份片号，从1 开始到创建的文件数
# %U 一个唯一的文件名，代表%u_%p_%c
# %s 备份集的号
# %t 备份集时间戳
# %T 年月日格式(YYYYMMDD)
###################################################
#FULL BACKUP (Every firday full backup at 21:00)
###################################################
Export dt=`date +%Y%m%d`
Export ORACLE_SID=
EXPORT ORACLE_HOME=
Rman nocatalog target username/password@$ORACLE_SID msglog /BACKUP/LOG/$ORACLE_SID_$dt.log append <
run {
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Allocate channel ch03 type disk;
backup
full
tag 'full_db'
format '/backup/Full_%d_%T_%s_%p'
database;
sql 'alter system archive log current';
backup archivelog all delete input;
release channel ch01;
release channel ch02;
release channel ch03;
}
########################################################################
#Incremental backups (Every Monday to firday backup archive log)
########################################################################
Export dt=`date +%Y%m%d`
Export ORACLE_SID=
EXPORT ORACLE_HOME=
Rman nocatalog target username/password@$ORACLE_SID msglog /u01/$ORACLE_SID_$dt.log append <
run {
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Allocate channel ch03 type disk;
sql 'alter system archive log current';
backup
format '/backup/arc_%d_%T_%s_%p'
backup archivelog all delete input;
release channel ch01;
release channel ch02;
release channel ch03;
}
*BACKUP ARCHIVELOG UNTIL TIME 'SYSDATE-7';根据时间备份日志（待测试）
######################################################################
#Delete 14 days before the archive log
######################################################################
Rman nocatalog target username/password@$ORACLE_SID msglog /u01/$ORACLE_SID_$dt.log append <
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE disk;
DELETE NOPROMPT BACKUP OF DATABASE COMPLETED BEFORE 'SYSDATE-14';
######################################################################
#Delete the last backup
######################################################################
Delete命令可以用来删除指定的备份或者用来删除废弃或者是过期的备份集
如删除指定的备份集与备份片
RMAN> DELETE BACKUPPIECE 101;
RMAN> DELETE CONTROLFILECOPY '/tmp/control01.ctl';
RMAN> DELETE BACKUP OF TABLESPACE users DEVICE TYPE sbt;
删除过期或者废弃了的备份
RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> DELETE OBSOLETE REDUNDANCY = 3;
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;
删除指定的备份归档
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 300;
#####################################################################
#
#####################################################################
Crosscheck命令
检查磁盘或磁带上的备份或拷贝是否正确，并更新备份或者拷贝的状态
如果不正确，将标记为expired（过期）
Crosscheck backup;
Crosscheck archivelog all;
Delete [noprompt] expired backup命令删除过期备份
也可以用List来查看相应的报告
LIST EXPIRED BACKUP;
LIST EXPIRED BACKUP SUMMARY;
