冷备份


数据库要正常停止! ( >= shutdown immediate)
保证数据库是正常的、可用的再作备份！
冷备份的数据库还原成功后数据状态和备份时的数据状态是一样的！
备份之后所发生的数据改变没有追加进来！

思考需要备份哪些文件？按数据库启动流程去寻找！
parameter file -> control file -> datafile -> redo log file
是否备份 listener.ora / pwdfile ? 


创建备份的目录:
su - oracle
mkdir /home/oracle/bk


生成拷贝数据库文件的脚本:
set pagesize 200
set linesize 160
select 'cp -v '||name||' /home/oracle/bk/'
from
(select name from v$controlfile
union all
select name from v$datafile
union all
select member from v$logfile);


cd /home/oracle/bk
vi bk.sh
--------------------------------------------------------------------------------
sqlplus /nolog @/home/oracle/bk/shut.txt
cp -v /u1/oracle/product/10g/dbs/orapwmadrid /home/oracle/bk/
cp -v /u1/oracle/product/10g/dbs/spfilemadrid.ora /home/oracle/bk/
--使用查询生成的结果集
sqlplus /nolog @/home/oracle/bk/start.txt
--------------------------------------------------------------------------------
chmod +x bk.sh


vi shut.txt
--------------------------------------------------------------------------------
conn / as sysdba
shutdown immediate
exit
--------------------------------------------------------------------------------


vi start.txt
--------------------------------------------------------------------------------
conn / as sysdba
startup
exit
--------------------------------------------------------------------------------


##############################
[oracle@10g dump]$ cat codebackup.sql 
conn / as sysdba
set heading off;
set linesize 100;
set verify off;
set feedback off;
spool backup.sh;
select '\cp -vf ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora /home/oracle/dump/' from dual;
--create pfile='/home/oracle/dump/pfileorcl.ora' from spfile;
select '\cp -vf '||name||' /home/oracle/dump/' from v$controlfile where rownum=1;
select '\cp -vf '||name||' /home/oracle/dump/' from v$datafile;
spool off;
shutdown immediate;
ho bash backup.sh
conn / as sydba
startup
exit;
##############################


备份成功之后校验备份文件的有效性:
dbv file=/home/oracle/bk/system01.dbf blocksize=8192


for i in $(find /home/oracle/bk/ -name "*.dbf");do dbv file=$i blocksize=8192;done

冷备份异地还原
1. 创建必要的目录
数据文件目录 / 日志文件目录 / 控制文件目录 / trace 目录 / audit 目录
2. 恢复参数文件，nomount

3. 恢复控制文件，mount
控制文件的位置，需要parameter file中的control_files指定位置相同

4. 恢复数据文件
select name from v$datafile
可用alter database rename file 'xxx' to 'xxx'修改
5. 恢复日志文件
select member from v$logfile
可用alter database rename file 'xxx' to 'xxx'修改
6. open数据库

备份单个表空间
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
Back up the offline datafiles. For example:
% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf

备份文本的控制文件
alter database backup controlfile to trace;

#######################################
a. 创建pfile，如果有备份spfile，用strings spfile.ora > pfile.ora
# 修改control_files
# 创建{a,b,c,u}dump

# 若没有备份controlfile，则需重建controlfile
# alter database backup controlfile to trace


[oracle@10g dump]$ cat c.sql 
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16 # noresetlog，system_scn 取当前日志组的high scn
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
 '/u01/app/oracle/oradata/oray/redo01.log'  SIZE 50M, --主要不要指定group
 '/u01/app/oracle/oradata/oray/redo02.log'  SIZE 50M,
 '/u01/app/oracle/oradata/oray/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
 '/home/oracle/dump/system01.dbf',
 '/home/oracle/dump/undotbs01.dbf',
 '/home/oracle/dump/sysaux01.dbf',
 '/home/oracle/dump/users01.dbf',
 '/home/oracle/dump/example01.dbf',
 '/home/oracle/dump/mssm01.dbf',
 '/home/oracle/dump/assm01.dbf'
CHARACTER SET WE8ISO8859P1
;

b. 启动到mount
# select name from v$datafile,修改数据文件名或路径
# alter database rename file '/u01/app/oracle/oradata/orcl/users01.dbf' to '/home/oracle/dump/users01.dbf';
# select member from v$logfile,重建redo logfile
#  alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/oray/redo01.log';
# alter database clear logfile '/u01/app/oracle/oradata/oray/redo03.log';
# 若开了归档 alter database clear unarchived logfile '/oracle/app/oracle/oradata/dbblack1/redo02.log';
# alter database open


c. 创建temporary tablespace
# 自动创建， 在alert日志有体现




recover datgabase until cancel;
alter database open resetlogs;