Oracle10g手工备份恢复
10g r2冷备,恢复,复制数据库(是在归档模式下的)
1.介绍环境,sid=fox
2.写冷备脚本
vi coldbak.sql
set pagesize 0 linesize 32767 feedback off verify off trimpool on termout off trimout on serveroutput on
define fil=/home/oracle/cdbk.sql
define log=/home/oracle/cdbk.log
spool &fil
prompt connect sys/qkhhfqp@fox as sysdba;
prompt shutdown immediate;
select 'host cp '||file_name|| '/manualbak/fox'||substr(file_name,instr(file_name,'/',-1,1)) from dba_data_files;
select 'host cp '||name|| '/manualbak/fox'||substr(name,instr(name,'/',-1,1)) from V$controlfile;
select 'host cp '||member|| '/manualbak/fox'||substr(member,instr(member,'/',-1,1)) from V$logfile;
prompt connect / as sysdba;
prompt startup;
spool off
spool &log
@&fil
spool off
别名:(使用方便)
alias rmanfox='rlwrap rman target sys/qkhhfqp@fox catalog rman10asm/rman@asmdemo'
alias rmansmk='rlwrap rman target sys/qkhhfqp@smk catalog rman10asm/asmdemo'
alias sqlasm='rlwrap sqlplus sys/qkhhfqp@asm as sysdba'
alias sqlasmdemo='rlwrap sqlplus sys/qkhhfqp@asmdemo as sysdba'
alias sqlfox='rlwrap sqlplus sys/qkhhfqp@fox as sysdba'
ORA-32004: obsolete and/or deprecated parameter(s) specified (是由于undo表空间在参数文件配置不正确,复制数据库的时候尤其要注意)
3. 执行脚本
3.1删除non-system tablespace数据文件
online恢复数据库 (方法,将数据文件offline,cp过来,recover datafile ...,将数据文件online或者用数据文件所在的表空间也可以)
3.2删除system表空间数据文件
在mount状态恢复数据库(方法:shutdown immediate数据库,cp过来,startup mount,recover database,alter database open)
3.3 undo表空间数据文件
在线恢复undo表空间
删除undon表空间数据文件,插入或删除添加表数据。(恢复之前向undo表空间添加一个数据文件)
(方法:cp过来,recover datafile ...,将数据文件online或者用数据文件所在的表空间也可)
3.4删除联机日志
mount resetlogs
备份控制文件
(删除日志,alter system checkpoint;alter system switch logfile
shutdown immediate ,startup mount,alter database backup controlfile to trace
vi /01/oracle/admin/fox/udump/fox_ora_13957.trc(要知道他进程号,通过ps -ef)
利用这个创建一个建日志文件的脚本,运行这个脚本,recover database using backup controlfile until cancel,alter database open resetlogs)
create controlfile reuse set database "FOX" resetlogs archivelog
---(reuse set 表示可以改变数据库名和dbid,reuse和原来的dbid一样,用建一样结构辅助数据或物理备库)
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/u01/oradata/fox/redo_g1.ora' size 5m,
group 2 '/u01/oradata/fox/redo_g2.ora' size 5m,
group 3 '/u01/oradata/fox/redo_g3.ora' size 5m
datafile
'/u01/oradata/fox/system01.dbf',
'/u01/oradata/fox/sysaux01.dbf',
'/u01/oradata/fox/undotbs01.dbf',
'/u01/oradata/fox/users01.dbf',
'/u01/oradata/fox/example01.dbf'
CHARACTER SET AL32UTF8;
3.5删除控制文件
nomount resetlogs
(删除控制文件,alter system switch logfile,alter database datafile 5 offline(online) 写控制文件 shutdown immediate (abort)
重建控制文件,自动进mount状态,recover database,只能用alter database open resetlogs打开数据库)
3.6删除临时表空间
添加临时表空间数据文件
(删除临时表空间数据文件,执行一个排序,删掉临时数据文件,再添加一个另数据文件)
alter system switch logfile;
alter system archive log current;
select status,name from V$datafile;
select tablespace_name from dba_data_file where file_name='/..../..'
alter system checkpoint;
select * from dictionary where table_name like '%UNDO%';
recover database using backup controlfile until cancel;
tail -f $ORACLE_BASE/admin/fox/bdump/alert_fox.log
!oerr ora 19909 显示错误 host oerr ora 32004 ! ipcs ! free 查看系统使用内存情况.
select tablespace_name from dba_tablespace where contents='TEMPORARY';
alter tablespace temp add tempfile '/oradat/fox/temp01.dbf' size 100m autoextend off;
explain plan for select * from center.zx_gs_qyxx order by qync;
select * from table(dbms_xplain.display);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11364208/viewspace-344582/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11364208/viewspace-344582/