oracle导入导出学习(2)

buffer=1000000

log=/home/alvin/expbk/exp_tbs254.log

scp /opt/oracle/oradata/alvin/tbs254.dbf

oracle@192.168.2.31:/u01/oracle/oradata/ora10g/

conn sys/oracle@stu31 as sysdba

grant connect,resource to u1 identified by u1;

导出表空间的元数据

------------------------------- userid='sys/oracle@stu31 as sysdba' file=/home/alvin/expbk/tbs254.dmp tablespaces=tbs254 TRANSPORT_TABLESPACE=y

datafiles='/u01/oracle/oradata/ora10g/tbs254.dbf' buffer=1000000

log=/home/alvin/expbk/imp_tbs254.log

alter tablespace tbs254 read write;

将数据导入指定的表空间

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

五：全库模式 导出全库

------------------------------- userid=system/oracle

file=/home/alvin/expbk/tbs254.dmp full=y

buffer=1000000

log=/home/alvin/expbk/exp_tbs254.log

*****************************************************************************************************************

一停二拷三启动

/home/alvin/coldbk/shut.txt --------------------------- conn / as sysdba shutdown immediate exit

/home/alvin/coldbk/start.txt ---------------------------- conn / as sysdba startup exit

/home/alvin/coldbk/bk.sh

----------------------------------------------------------------- sqlplus /nolog @/home/alvin/coldbk/shut.txt

cp -v $ORACLE_HOME/dbs/orapwalvin /home/alvin/coldbk/

cp -v $ORACLE_HOME/dbs/spfilealvin.ora /home/alvin/coldbk/

cp -v /opt/oracle/oradata/alvin/control01.ctl /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/control02.ctl /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/control03.ctl /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/system01.dbf /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/undotbs01.dbf /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/sysaux01.dbf /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/users01.dbf /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/example01.dbf /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/redo03.log /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/redo02.log /home/alvin/coldbk/ cp -v /opt/oracle/oradata/alvin/redo01.log /home/alvin/coldbk/ sqlplus /nolog @/home/alvin/coldbk/start.txt

chmod +x /home/alvin/coldbk/bk.sh

校验备份的数据文件的有效性:

dbv file=/home/alvin/coldbk/system01.dbf blocksize=8192

批量校验:

for i in $(find /home/alvin/coldbk/ -name \blocksize=8192;done

------------------------------------------------------------------ 一致性全冷备份的异地还原: nomount mount open

*****************************************************************************************************************

数据库要运行在归档模式下:

archive log list

在数据库open状态下镜像备份控制文件:

alter database backup controlfile to '/home/alvin/hotbk/control01.ctl';

模拟数据库有交易:

alter system checkpoint; alter system switch logfile;

select cpods from x$kcccp;

还原控制文件

cp -v /home/alvin/hotbk/control01.ctl /opt/oracle/oradata/alvin/control01.ctl

恢复控制文件

recover database using backup controlfile;

alter database open RESETLOGS;

----------------------------------------------------------------- 重建控制文件:

mount & open生成重建控制文件的脚本:

alter database backup controlfile to trace as '/home/alvin/crectl.sql';

手工重建控制文件:

create controlfile reuse database alvin noresetlogs archivelog logfile

group 1 '/opt/oracle/oradata/alvin/redo01.log' size 50m, group 2 '/opt/oracle/oradata/alvin/redo02.log' size 50m, group 3 '/opt/oracle/oradata/alvin/redo03.log' size 50m datafile

'/opt/oracle/oradata/alvin/system01.dbf', '/opt/oracle/oradata/alvin/undotbs01.dbf', '/opt/oracle/oradata/alvin/sysaux01.dbf', '/opt/oracle/oradata/alvin/users01.dbf', '/opt/oracle/oradata/alvin/example01.dbf' character set we8iso8859p1;

alter database open;

select tablespace_name from dba_tablespaces where contents='TEMPORARY';

ALTER TABLESPACE TEMP ADD TEMPFILE

'/opt/oracle/oradata/alvin/temp01.dbf' REUSE;

--------------------------------------------------------------------- 热备份数据文件:

1.users表空间的数据文件:

alter tablespace users begin backup;

!cp -v /opt/oracle/oradata/alvin/users01.dbf /home/alvin/hotbk/ alter tablespace users end backup;

模拟数据改变: insert update delete

alter system checkpoint; alter system switch logfile; 模拟文件丢失:

rm -f /opt/oracle/oradata/alvin/users01.dbf

重新启动数据库: startup force

如果数据库不能open,将有问题的文件offline，再将数据库open： alter database datafile '/opt/oracle/oradata/alvin/users01.dbf' offline;

查看哪些文件需要恢复

select * from v$recover_file;

还原数据文件:

!cp -v /home/alvin/hotbk/users01.dbf /opt/oracle/oradata/alvin/

查看恢复时需要哪些日志

select * from v$recovery_log;

恢复数据文件:

recover datafile 4;

--------------------------------------------------------------------- system或undo表空间的文件丢失: 与users表空间丢失的处理方式有什么不同？ ＊还原和恢复都在mount状态下进行！ …… 此处隐藏：2421字，全部文档内容请下载后查看。喜欢就下载吧 ……