五、用户管理备份
(1)主要数据:oradata/* dbs/* arch/*
相关视图:v$datafile,v$controlfile,v$logfile,v$archived_log,v$tablespace,v$tempfile
(2)冷备:关闭-->copy-->启动
select name from v$datafile;
select name from v$controlfile;
--select member from v$logfile;
--select name from v$tempfile;
shutdown immediate;
df -hl
mkdir /backup
chown -R oracle:oinstall /backup
chmod -R 755 /backup
su - oracle
cd /backup
mkdir cold
mkdir hot
cd /backup/cold
cp -rf /u01/app/oracle/oradata/ocp/* .
startup
(3)热备:
SQL> col file_name for a50;
SQL> select tablespace_name, file_name from dba_data_files;
SQL> alter tablespace tp1 begin backup;
SQL> !cp /oradata/bxdb/user01.dbf /backup/
SQL> alter tablespace users end backup;
SQL> alter system switch logfile;
--dd if=/u01/app/oracle/oradata/ocp/tp1.dbf skip=205 bs=8192 count=2|strings
--alter system dump datafile block;
*批量操作脚本:(v$backup,dba_data_files,dba_tablespaces)
select
'alter tablespace '||tablespace_name|| ' begin backup;' ||chr(10)||
'host cp '||file_name||' /backup' ||chr(10)||
'alter tablespace '||tablespace_name|| ' end backup;'
from dba_data_files order by tablespace_name;
begin
for i in 1 .. 10000 loop
insert into t1 values(i,'gyj'||i);
commit;
end loop;
end;
/
(4)控制文件备份:
alter database backup controlfile to '/backup/control.bin';
alter database backup controlfile to trace as '/backup/control.ctl';
(5)初始化文件备份:create pfile='/backup/pfile.ora' from spfile;
(4)连接恢复目录(ppt-les_03_catalog)
create tablespace rc_data datafile '/u01/app/oracle/oradata/ocp/rc_data01.dbf' size 100m;
create user rc_admin identified by rc_admin default tablespace rc_data quota unlimited on rc_data;
grant connect,resource,recovery_catalog_owner to rc_admin;
rman catalog rc_admin/rc_admin
create catalog tablespace rc_data;
exit
rman target / catalog rc_admin/rc_admin
register database;
*******************************************************
虚拟专用目录:
create user v_user1 identified by v_user1 default tablespace tp1 temporary tablespace temp quota unlimited on tp1;
grant recovery_catalog_owner to v_user1;
[oracle@ocm ~]$ rman catalog rc_admin/rc_admin
grant catalog for database ocm to v_user1;
--查恢复目录 sqlplus rc_admin/rc_admin
select dbid,name,resetlogs_change# from rc_database;
select ts#,name,creation_change# from rc_tablespace;
select file#,name,bytes from rc_datafile;
select script_name from rc_stored_script;
select line,text from rc_stored_script_line;
3. RMAN备份
(1)备份数据库
backup database;
backup database format '/backup/rman_full_%U';
list backup of database;
(2)备份一个数据文件
report schema
select file#,name from v$datafile;
backup datafile 4 format '/backup/md_%U';
list backup of datafile 4;
(3)备份表空间
select tablespace_name,contents from dba_tablespaces;
backup tablespace trans format '/backup/trans_%U';
list backup of tablespace trans;
(4)备份控件文件(4种方法)
①自动备份:configure controlfile autobackup on;
②backup current controlfile;
③backup datafile 4 include current controlfile;
④sql "alter database backup controlfile to ''/backup/control/back_controlfile.bin''";
list backup of controlfile;
(5)备份参数文件(2种方法)
①自动备份:configure controlfile autobackup on;
②backup spfile format '/backup/spfile.ora';
(6)备份归档文件
①backup archivelog all format '/backup/arch_%U';
②backup archivelog sequence between 85 and 89 delete all input;
③backup archivelog from time "sysdate - 14" until time "sysdate - 7 ";
④backup database plus archivelog format '/back/rman/full_%U' delete all input;
list backup of archivelog all;
(7)备份备份集
①backup backupset 1 format '/backup/backupset1_%U';
②backup backupset all;
(8)镜像拷贝
①只能拷贝到磁盘上
②恢复(restore)只要switch,速度快
③backup copy 或直接用copy
copy datafile 5 to '/u01/app/oracle/oradata/bxdb/tp5.dbf ';
--copy datafile 5 to '+dg/oradata/bxdb/tp5.dbf ';
(9)多条命令
run {allocate channel c1 type disk;
maxpieces size=2G;
backup format '/backup/backup_%u' filesperset 3 database;
}
run {allocate channel d1 type disk;
format '/backup/all_%u' maxpieces=100m;
backup database filesperset=3;
}
七、RMAN常用命令
5.1 显示默认配置 --show命令
show all;
show controlfile autobackup;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%U';
set nocfau;--关闭自动备份控制文件和参数文件
5.2 列出备份信息 --list命令
list backup;
list backup of controlfile;
list backup of datafile '/oradata/bxdb/users.dbf';
list backup of 5;
list backup of archivelog all;
list copy of tablespace 'SYSTEM';
list device type disk backup;
list archivelog all;
list expired backup;
5.3 删除备份 --delete 命令
delete obsolete; --备份策略
delete expired backup; --物理路径
delete expired copy;
delete expired archivelog all;
delete backupset 5;
delete backuppiece '/backup/user_demo_20.bak';
delete backup;
delete datafile copy '/oradata/bxdb/users.dbf';
delete copy;
backup archivelog all delete input;
5.4 报表显示 --report命令
report schema;
report need backup;
report need backup tablespace system;
report obsolete;
5.5 执行检查 --crosscheck命令
crosscheck archivelog all;
crosscheck backup;
5.6 配置configure