RMAN sql> sqlplus / as sysdba 查看数据库版本 sql> select * from v$version; 查看数据库名称 sql> show parameter db_name; 一 使用 RMAN 时, 需要将数据库设置成归档模式 sql> conn / as sysdba; sql> show user 查看数据库是否为归档模式 sql> archive log list 开启归档模式 关闭数据库 sql> shutdown immediate 启动数据库到 mount 状态
sql> startup mount 改变数据库的归档模式 sql> alter database archivelog # 打开数据库 sql> alter database open 禁止归档模式 sql> shutdown immediate sql>startup mount sql> alter database noarchivelog sql> alter database open 设置 rman 备份环境参数及自动备份控制文件 查看 RMAN 备份环境 查看所有参加配置 rman> show all 启动控制文件的自动备份 rman> configure controlfile autobackup on; rman> show controlfile autobackup; 全库备份
rman> backup database; 查看备份信息 rman> list backup; 查看备份的总体信息 rman> list backup summary; 二 编写脚本 设置 rman rman target/ rman> configure retenticn policy to redundancy 10; o 级全库备份 $ vim BackupFull.sh # script.:bakupfull.sh # creater:zihaowu # date:2015/07/28 # desc:backup full database datafile in archive with rman # connect database export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 export ORACLE_SID=oms export PATH=$ORACLE_HOME/bin:$PATH
rman target/ << EOF_RMAN run{ allocate channel c1 type disk; backup incremental level tag 'db0' format '/u01/app/oracle/rmanbackup/db0_%d_ %T_%s' database include current controlfile; delete noprompt obsolete; release channel c1; } # end 1 级增量备份 Vim Incr1.sh # script.:incr1.sh # creater:zihaowu # date:2015/07/28 # desc:backup full database datafile in archive with rman connect database export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 export ORACLE_SID=oms export PATH=$ORACLE_HOME/bin:$PATH rman target/ << EOF_RMAN run{ allocate channel c1 type disk; backup incremental level 1 tag 'db1' format '/u01/app/oracle/rmanbackup/db1_%d_ %T_%s' database include current controlfile; delete noprompt obsolete;
release channel c1; } # end 二 恢复 ( 一 ) 完全恢复数据文件 # rman target/ 查看改数据库是否有备份 rman> list backup summary; # cd /u01/app/oracle/oradata/oms # rm *.dbf # ls # sqlplus / as sysdba sql> shutdown immediate sql> shutdown abort sql> startup # rman target/ rman> restore database; rman> recover database;
rman> sql 'alter database open'; rman> exit # sqlplus / as sysdba sql> select instance_name,status from v$instance; sql> archive log list; ( 二 ) 重做日志文件的硬盘坏掉, 如何重新生成 rman> list backup; list backup summary; $ cd /u01/app/oracle/oradata/oms $ rm *.log $ ls *.log $ sqlplus / as sysdba 切换 Oracle 数据库的重做日志 sql> alter system switch logfile; sql> shutdown immediate sql> startup sql> recover database until cancel; sql> ls
sql> alter database open resetlogs; sql> archive log list; ( 三 ) 数据库丢失数据文件 控制文件 重做日志文件 # rman target/ rman> list backup; # cd /u01/app/oracle/oradata/oms # ls # rm *.* # sqlplus / as sysdba sql> select count(*) from bankuser.emp; sql> shutdown abort sql> startup sql> select instance_name,status from v$instance; # rman target/ rman> restore controlfile from autobackup; rman> alter database mount; rman> restore database;
sqlplus / as sysdba sql> recover database using backup controlfile until cancel; sql> alter database open resetlogs; sql> archive log list; ( 四 ) 丢失数据文件 控制文件 重做日志 初始化文件 1. 备份 备份数据文件 归档日志文件 控制文件 初始化文件 查看数据库的备份 rman> list backup; 查看数据库的 spfile 初始化文件 # sqlplus / as sysdba sql> show parameter spfile; 2. 模拟丢失数据文件 重做日志文件 控制文件 # cd /u01/app/oracle/oradata/oms # ls # rm *.* 删除初始化文件 spfile
# cd /u01/app/oracle/product/10.2.0/db_1/dbs # rm spfileoms.ora 3. 启动 # sqlplus / as sysdba sql> select count(*) from bankuser.emp; sql> shutdown abort sql> startup 报错 : 提示找不到初始化文件 sql> select instance_name,status from v$instance; 恢复初始化文件 : $ sqlplus / as sysdba sql> startup pfile='/u01/app/oracle/admin/oms/pfile/init.ora.210201018935'; sql> select instance_name,status from v$instance; rman> restore spfile from autobackup; $ sqlplus / as sysdba sqlplus> shutdown immediate; sql> startup nomount;
sql> show parameter spfile; 恢复控制文件 rman> restore controlfile from autobackup; 恢复数据文件 rman> alter database mount; rman> restore database; 数据库恢复 $ sqlplus / as sysdba sql> recover database using backup controlfile until cancel; 重做日志文件 sql> alter database open resetlogs; sql> archive log list; ( 五 ) 数据库恢复到莫个时间点 属于数据库的不完全恢复 数据库设置时间格式
sql> alter session set nls_date_format='yyyymmdd hh24:mi:ss'; 查看数据库当前时间 sql> select sysdate from dual; sql> conn bankuser/bankpwd sql> drop table emp; sql> conn scott/tiger sql> drop table salgrade; 数据库恢复到莫个时间点 sql> conn / as sysdba sql> shutdown immediate 如果 linux 系统中有多个数据库实例 $ export ORACLE_SID=oms rman> startup mount rman> restore dababase; rman> sql 'alter session set nls_date_format= yyyymmdd hh24:mi:ss '; rman> recover database until time '20110414 11:33:56'; rman> sql 'alter database open resetlogs'; $ sqlplus / as sysdba sql> archive log list;
验证 sql> conn bankuser/bankpwd sql> select count(*) from emp; sql> conn scott/cat; sql> select count(*) from sqlgrade; ( 六 ) 数据库恢复到某个 SCN 查找当前数据库的 SCN 号 sql> conn / as sysdba sql> select dbms_flashback.get_system_change_number from dual; SCN 号和数据库时间对应 sql> select to_char(scn_to_timestamp(963959),'yyyy-mm-dd HH24:MI:SS') from dual; 根据数据库的时间来找到对应的数据库 SCN 号 sql> select timestamp_to_scn(to_date('2011-04-14 12:26:19','YYYY-MM-DD HH24:MI:SS')) from dual; sql> conn bankuser/bankpwd sql> drop table emp; sql> drop table dept; sql> conn scott/tiger sql> drop table bonus;
恢复 sql> conn / as sysdba sql> alter session set nls_date_format='yyyymmmdd hh24:mi:ss'; sql> select* from (select time_dp,scn from smon_scn_time order by time_dp desc) where rownum<10; sql> shutdown immediate $ export ORACLE_SID=oms rman> startup mount rman> restore database; rman> recover database until scn 963915; rman> sql 'alter database open resetlogs'; 验证 $ sqlplus / as sysdba sql> archive log list;