OCP_07

1.backup

a.archivelog mode
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled --开启
Archive destination            USE_DB_RECOVERY_FILE_DEST  --&gtFRA
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停止工作，连不进去（操作---&gtredo--&gtswitch,生成archivelog---但是空间满了，生成不了archivelog,因此数据hang住------清理日志）

1.一定分区使用率监控，比如85%报警（短信、email）
2.linux --&gtcrontab , （a.备份后清除 b.rm,比如清除3天的日志 c策略,比如传到备库后就删除）
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO applied on all standby; ---&gt在备库设置，主库的日志传过来应用好后删除
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO shipped to all standby; ---&gt在主库设置，日志传到备库即可删除
 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----初始化
备份策略--&gt公司标准，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 --&gtOK

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 data_guard（同机房，同城，异地）
adg-- active data guard
a.switch over standby (10M)
   激活standby


b.DB 18TB(36H以上）
 
oracle duplicate----11 standby 

standby 
1.primary:在主库备份，全库+controlfile(for standby)  ----&gt11g不用备份
2.standby:spfile ---&gtnomount
3.standby:restore controlfile ---&gtmount
4.standby:restore database ---&gtrestore datafile   ---&gtduplicate
recover database
5. primary                 listener(静态注册),password file(copy from primary)
5.app--&gtprimary(archive log)---&gtstandby (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.  主库：
a.开启archivelog 
b.alter database  force logging;
select  FORCE_LOGGING    from v$database; --YES

2备库
1..listener.ora(静态)
standby:
SID_LIST_LISTENER=
(SID_LIST=
 (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

lsnrctl status 
Instance "tk", status UNKNOWN, has 1 handler(s) for this service...

3.主库+备库，都需要做
tnsname.ora
tk_242 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tk)
    )
  )

tk_199 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tk)
    )
  )

tnsping tk_199
tnsping tk_242

4.备库 pasword file
常规：
 scp  orapwtk oracle@192.168.1.242:~ --- > $ORACLE_HOME/dbs

新建： orapwd  file=$ORACLE_HOME/dbs/orapwtk password=oracle

综合测试，如果能登录进去，说明listner.ora, tnsnames.ora,pasword file的配置都没有问题:
rman target sys/oracle@tk_199 auxiliary sys/oracle@tk_242  --OK

5.standby创建spfile
a.primary
create pfile='/tmp/tk.ora' from spfile;
b.copy 
scp /tmp/tk.ora  oracle@192.168.1.105:/home/oracle
c.modify
*.audit_file_dest='/usr/app/oracle/admin/tk/adump' 
*.control_files='/u02/app/oracle/fast_recovery_area/tk/control02.ctl','/u02/app/oracle/oradata/tk/control01.ctl'#Restore Controlfile
*.db_recovery_file_dest='/u02/app/arch'
*.log_archive_dest_1='location=/u02/app/arch'
*.log_file_name_convert='/u01/app/oracle/oradata/tk','/u03/app/oracle/oradata/tk' --主备库存放data file的路径不一样时必须要加次参数
*.db_file_name_convert='/u01/app/oracle/oradata/tk','/u03/app/oracle/oradata/tk'--主备库存放redo log file 的路径不一样时必须要加次参数

*以上参数的路径需要关注，并根据实际情况修改，针对没有的路径要创建，并赋予oracle用户的权限


[root@localhost ~]# mkdir -p /u02/app/oracle/oradata/tk
[root@localhost ~]# chown -R oracle:oinstal /u02

test :
startup pfile='/home/oracle/tk.ora' nomount;
create spfile from pfile='/home/oracle/tk.ora';
shutdown immediate;
startup nomount;

6.rman target sys/oracle@tk_199 auxiliary sys/oracle@tk_242 
duplicate target database  for standby from active database;

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

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

1.日志同步
--传归档日志：archive_lag_target=900 --强制15分钟切换一次日志
primary:
SQL> alter system set log_archive_dest_2='service=tk_242';

standby :
startup mount;
alter database recover managed standby database disconnect from session;

--传redo log  ----standby log -- Real Time Apply
primary:
SQL> alter system set log_archive_dest_2='service=tk_242 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=tk';

standby :
add standby redo log ---n+1(p:3 , st:4)
alter database add standby logfile  group 6 ('/u03/app/oracle/oradata/tk/stredo03.log', '/u03/app/oracle/oradata/tk/stredo04.log') size 50m;
alter database add standby logfile  group 7 ('/u03/app/oracle/oradata/tk/stredo05.log', '/u03/app/oracle/oradata/tk/stredo06.log') size 50m;
alter database add standby logfile  group 8 ('/u03/app/oracle/oradata/tk/stredo07.log', '/u03/app/oracle/oradata/tk/stredo08.log') size 50m;
alter database add standby logfile  group 9 ('/u03/app/oracle/oradata/tk/stredo09.log', '/u03/app/oracle/oradata/tk/stredo10.log') size 50m;

startup mount;
alter database recover managed standby database disconnect from session;

alter database open; ---11g:adg(active data guard)
alter database recover managed standby database using current logfile disconnect;


SQL> alter system set fal_client=tk_242;

System altered.

SQL>  alter system set fal_server=tk_199;

System altered.
------------------------------------------------------------------------------------------------------
switch over 

standby:
 create restore point p1 guarantee flashback database;
 flashack database to restore  point p1;
 drop restore point p1; --mount
primary:
 create restore point p2 guarantee flashback database;
 flashback database to restore  point p2;
 drop restore point p2; --open

select switchover_status from v$database;

primary---status:open 
alter database commit to switchover to physical standby with session shutdown;
recover database; --选择auto
standby---status:mount
alter database commit to switchover to primary with session shutdown ;
recover standby database;--选择auto
---------------------------------------------------------------------------------------------


switch over 切换演练  
激活standby 验证
primary:
alter system set log_archive_dest_state_2=defer;

standby:
alter database recover managed standby database cancel;
create restore point p3 guarantee flashback database;
aler database open read only; --先试一下能不能open read only;
shutdown immediate;
startup mount;
alter database activate standby database; 
alter database open; ---read write
select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
create table bb as select * from v$database; ---OK
shutdown immediate;  
startup mount;
flashback database to restore  point p3; --MOUNT
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database open; --read only
alter database recover managed standby database using current logfile disconnect;
drop restore point p3; --open

primary:
 alter system set log_archive_dest_state_2=enable;
________________________________________________________

copy datafile 7 to '/u01/app/oracle/oradata/tk/users02.dbf';
sql 'alter database  datafile 7 offline';
switch datafile 7 to copy;
recover datafile 7;
sql 'alter database  datafile 7 online';
---------------------
select * from v$rman_status
catalog 
rsr

SQL>  create tablespace catalog datafile '/usr/app/oracle/oradata/orcl/catalog01.dbf' size 100m;
Tablespace created.
SQL> create user rman identified by rman default tablespace catalog quota unlimited on catalog;
User created.
SQL> grant  recovery_catalog_owner to rman ;
[oracle@localhost ~]$ rman catalog rman/rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 4 15:04:48 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

[oracle@lidandan admin]$ rman target / catalog rman/rman@orcl_105 

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 4 01:08:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TK (DBID=2552682434)
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.01 in RCVCAT database is not current
PL/SQL package RMAN.DBMS_RCVMAN version 11.02.00.01 in RCVCAT database is not current


RMAN> upgrade catalog;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>  UPGRADE CATALOG;
recovery catalog upgraded to version 11.02.00.03
DBMS_RCVMAN package upgraded to version 11.02.00.03
DBMS_RCVCAT package upgraded to version 11.02.00.03
RMAN> register database;

[oracle@lidandan admin]$ rman target / catalog rman/rman@orcl_105 
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 4 01:12:55 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TK (DBID=2552682434)
connected to recovery catalog database

RMAN> backup database;
select * from db
select * from rsr  --- v$rman_status

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1097152/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1097152/