a.archivelog mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled --开启
Archive destination USE_DB_RECOVERY_FILE_DEST -->FRA
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
shutdown immediate;
startup mount;
alter database archivelog ; 开归档
alter database open;
归档日志存放：---初始化
1.文件系统
alter system set log_archive_dest_1='location=/u01/app/arch1';
2.FRA（flash_recovery_area） ---建议
dest不定义默认FRA
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' ---明确定义
db_recovery_file_dest --真实位置
db_recovery_file_dest_size --大小，建议至少3~7天的归档
alter system switch logfile;
区别：
删除策略
删除不及时风险，归档满，DB停止工作，连不进去（操作--->redo-->switch,生成archivelog---但是空间满了，生成不了archivelog,因此数据hang住------清理日志）
1.一定分区使用率监控，比如85%报警（短信、email）
2.linux -->crontab , （a.备份后清除 b.rm,比如清除3天的日志 c策略,比如传到备库后就删除）
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO applied on all standby; --->在备库设置，主库的日志传过来应用好后删除
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO shipped to all standby; --->在主库设置，日志传到备库即可删除
CONFIGURE ARCHIVELOG DELETION POLICY clear--改回默认值
*如果选择备份后清除，另外增加监控备份是否成功。
FRA和文件系统区别
FRA备份了会自动清理，文件系统一定要写脚本清理（backup databae plus archivelog delete input）
-------------------------------------------------------------------------------------------------------------------------
backup as compressed backupset database;
备份策略
backup as compressed backupset incremental level=0 database; ---与全备内容一样，可以继续做增量备份
backup incremental level=1 database; ---自上一次0级之后所有的更改的备份
backup incremental level=2 database; ---自上一次的0/1/2之后更改
1.
周日（0）--周一（2）--周二（2）--周三（1）--周四（2）--周五（2）-周六（2）
2.smail db
0（最空闲的时间点，IO,CPU）+归档日志 （备份机保留策略，15~30天）
3.big db
0(周六)+1（周一到周五、周日）
*提升恢复时效
---------------------------------------------------
块跟踪
alter database enable block change tracking using file 'rman_inc_trk';
select * from v$block_change_tracking;
DBA,归档（文件系统，FRA），个人推荐FRA----初始化
备份策略-->公司标准，1.考虑备份性能消耗对业务系统影响 2.恢复时效
备份保留策略
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE RETENTION POLICY TO recovery window of 18 days;
report obsolete REDUNDANCY 2;
delete noprompt obsolete REDUNDANCY 2;
rm o1_mf_nnndf_TAG20130802T192555_8zrtkn6t_.bkp
Status: AVAILABLE Compressed: YES Tag: TAG20130802T192555
crosscheck backup;
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/arch/TK/backupset/2013_08_02/o1_mf_nnndf_TAG20130802T192555_8zrtkn6t_.bkp RECID=11 STAMP=822425156
delete noprompt expired backup;
rman monitor
SELECT s.sid, p.spid, s.client_info
FROM v$process p ,v$session s
WHERE p.addr = s.paddr
AND CLIENT_INFO LIKE 'rman%';
* 操作系统级别kill -9杀掉RMAN的进程后，DB里的会话如果没有跑完会继续跑，需要将DB的有关RMAN的会话杀掉。
SELECT OPNAME, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
__________________________________________________________
lost pasword file
lost datafile (system , nosystem)
lost controlfile
show parameter control_files
lost one controlfile
/u01/app/oracle/oradata/tk/control01.ctl, /u01/app/oracle/fast_recovery_area/tk/control02.ctl
改controlfile参数，去掉损坏的controlfile,用其他的启动看是否能成功
SQL> alter system set control_files='/u01/app/oracle/fast_recovery_area/tk/control02.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup nomount; ----可以成功
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/fast_recovery_
area/tk/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter database mount; ---OK
SQL> alter database open; --OK
SQL> alter system set control_files='/u01/app/oracle/fast_recovery_area/tk/control02.ctl','/u01/app/oracle/oradata/tk/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
cp /u01/app/oracle/fast_recovery_area/tk/control02.ctl /u01/app/oracle/oradata/tk/control01.ctl
startup -->OK
lost all control file :
list failure;
list failure 1120,1223,1160 detail;
advise failure;
repair failure preview;
repair failure;
select * from v$ir_failure
select * from v$ir_manual_checklist
select * from v$ir_repair
select * from v$ir_failure_set
# restore control file
restore controlfile from autobackup;
sql 'alter database mount';
*restore控制文件较旧，与数据文件不一致
# recover database
recover database;
alter database open resetlogs;
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the using backup controlfile option must be done
recover database using backup controlfile;
---控制文件，多路复用
---------------------------------------------------------------------------------------------------------------------
lost redolog
group member
1.add logfile group
alter database add logfile group 4 '/u01/app/oracle/oradata/tk/redo04.log' SIZE 50M
2.add logfile member
alter database add logfile member '/u01/app/oracle/oradata/tk/redo05.log' to group 1 ;
status :inactive,active, current,UNUSED
lost one member of group
rm /u01/app/oracle/oradata/tk/redo01.log
alerttk.log 有报错，DB正常
Errors in file /u01/app/oracle/diag/rdbms/tk/tk/trace/tk_arc1_23407.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/tk/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
alter database drop logfile member '/u01/app/oracle/oradata/tk/redo01.log' ;
alter database add logfile member '/u01/app/oracle/oradata/tk/redo01.log' to group 1 ;
lost all member
1.inactive
group 1 : redo01.log , redo05.log
alter system switch logfile; ---正常切
alerttk.log
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/tk/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/tk/redo05.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance tk - Archival Error
alter database clear logfile group 1;
alter database clear unarchived logfile group 1; --没有归档
DB可以正常打开的
alter database drop logfile group 1;
重新添加
2.current/active
group 4: rm redo04.log, redo08.log
alter system switch logfile; --切不动，alert一直报错
shutdown abort;
startup mount;
SQL> alter database clear logfile group 4;
alter database clear logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance tk (thread 1) needs to be archived
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/tk/redo04.log'
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/tk/redo08.log'
SQL> alter database clear unarchived logfile group 4;
Database altered.
--如果上一步不能成功需要基于备份做不完全恢复。
SQL> alter database open;
Database altered.
archive log list ----sequence
不完全恢复：
rman target /
{
set until sequence 5 thread 1:
restore database ;
recover database;
alter database open resetlogs;
}
export NLS_LANG = american_america.AL32UTF8
export NLS_DATE_FORMAT = "yyyy-mm-dd:hh24:mi:ss"
shutdown immediate
startup mount
RUN
{
SET UNTIL TIME '2007-08-14:21:59:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
SQL 'ALTER DATABASE OPEN READ ONLY';
ALTER DATABASE OPEN RESETLOGS;
-------------------------------------------------------------------------------------------
1.架构的层面
DB---standby（同机房，同城，异地）
a.switch over standby (10M)
激活standby
b.DB 18TB(36H以上）
oracle duplicate----11 standby
standby
1.primary:在主库备份，全库+controlfile(for standby) ---->11g不用备份
2.standby:spfile --->nomount
3.standby:restore controlfile --->mount
4.standby:restore database --->restore datafile --->duplicate
5. primary<---->standby ---net services name
listener(静态注册),password file(copy from primary)
5.app-->primary(archive log)--->standby (archive log , applied)
alter system set log_archive_dest_1='service=
5.recover database / alter database recover managed standby database disconnect from session; ---等待同步
standby
10g vs 11g
10g alter database recover managed standby database disconnect from session; （mount）
alter database open read only;(不能应用日志)
11g 可以一边查应用日志，DB,报表（select）
11g duplicate
1.archivelog
2.primary:
SQL> select FORCE_LOGGING from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
3.listener.ora(P+S)
standby:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(ORACLE_HOME=/usr/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC=
(SID_NAME=tk)
(ORACLE_HOME=/usr/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /usr/app/oracle
primary---->tnsname.ora
TK_105 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tk)
)
)
tnsping TK_105
standby ---->tnsname.ora
TK_199 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tk)
)
)
tnsping TK_199
primary
alter system set log_archive_dest_2='service=TK_105';