手工冷备份脚本

转载

mb5ff981a16d1dd 2013-09-20 16:56:00

文章标签 oracle sql 数据文件控制文件 javascript 文章分类 代码人生

1、新建两个目录，分别为cold_bak和hot_bak，分别用作冷备份和热备份转储的位置。

[oracle@rtest oracle]$ cd bak

[oracle@rtest bak]$ mkdir cold_bak

[oracle@rtest bak]$ mkdir hot_bak

[oracle@rtest bak]$ pwd

/u01/app/oracle/bak

[oracle@rtest bak]$ ls

cold_bak hot_bak

2、冷备份，即拷贝数据文件和控制文件到新的位置，转储。

首先查看需要备份的数据文件和控制文件的位置。

sys@TEST0910> select name from v$datafile order by 1;

NAME

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

/u01/app/oracle/oradata/test0910/example01.dbf

/u01/app/oracle/oradata/test0910/sysaux01.dbf

/u01/app/oracle/oradata/test0910/system01.dbf

/u01/app/oracle/oradata/test0910/testtb.dbf

/u01/app/oracle/oradata/test0910/undotbs01.dbf

/u01/app/oracle/oradata/test0910/users01.dbf

6 rows selected.

sys@TEST0910> select name from v$controlfile order by 1;

NAME

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

/u01/app/oracle/fast_recovery_area/test0910/control02.ctl

/u01/app/oracle/oradata/test0910/control01.ctl

3、拼SQL语句。执行操作系统拷贝命令。

数据文件的拷贝命令

sys@TEST0910> set verify on

sys@TEST0910> select 'host cp ' ||name|| ' &bakdir' from v$datafile order by 1;

Enter value for bakdir: /u01/app/oracle/bak/cold_bak

old 1: select 'host cp ' ||name|| ' &bakdir' from v$datafile order by 1

new 1: select 'host cp ' ||name|| ' /u01/app/oracle/bak/cold_bak' from v$datafile order by 1

'HOSTCP'||NAME||'/U01/APP/ORACLE/BAK/COLD_BAK'

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

host cp /u01/app/oracle/oradata/test0910/example01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/sysaux01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/system01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/testtb.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/undotbs01.dbf /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/users01.dbf /u01/app/oracle/bak/cold_bak

6 rows selected.

控制文件的拷贝命令

sys@TEST0910> select 'host cp ' ||name|| ' &bakdir' from v$controlfile order by 1;

Enter value for bakdir: /u01/app/oracle/bak/cold_bak

old 1: select 'host cp ' ||name|| ' &bakdir' from v$controlfile order by 1

new 1: select 'host cp ' ||name|| ' /u01/app/oracle/bak/cold_bak' from v$controlfile order by 1

'HOSTCP'||NAME||'/U01/APP/ORACLE/BAK/COLD_BAK'

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

host cp /u01/app/oracle/fast_recovery_area/test0910/control02.ctl /u01/app/oracle/bak/cold_bak

host cp /u01/app/oracle/oradata/test0910/control01.ctl /u01/app/oracle/bak/cold_bak

冷备脚本：

[oracle@rtest bak]$ vi cold_bak.sql

set echo off trimspool off heading off feedback off verify off time off

set pagesize 0 linesize 200

define bakdir='/u01/app/oracle/bak/cold_bak'

define bakscp='/u01/app/oracle/bak/cold_cmd.sql'

spool &bakscp

select 'host cp ' ||name|| ' &bakdir' from v$datafile order by 1;

select 'host cp ' ||name|| ' &bakdir' from v$controlfile order by 1;

spool off

shutdown immediate

@&bakscp

startup

执行冷备脚本：

sys@TEST0910> @/u01/app/oracle/bak/cold_bak