我也是接触oracle不久,很多不懂的地方,现有一个需求:要写一个脚本对远程服务器的oracle数据库进行冷备份,要求是要备份到另外一个远程服务器上,现在不考虑从服务器A备份到服务器B,我现在想搞清楚的是怎么从服务器端备份到本地来。还有一个我也不知道算不算重要的地方,就是服务器是linux系统,本地是windows的。
我在网上查了很久试着修改了点脚本,可在测试备份本机的oracle数据库,由于还有缺陷(最后备份完启动数据库时,无法自行完成),还没远程测试备份过、现貼出来,望高手们指教。 小弟不胜感激!文笔有限,若没看懂,提出,我详说。//oracle_cool_backup.bat
echo 定义时间日期变量
set date=%date:~0,10%
echo %date:/=-%
set h=%time:~0,2%
set m=%time:~3,2%
set s=%time:~6,2%
set tempvar=%date%-%h%-%m%-%s%
md "F:/app/Administrator/oradata/coldbackup/%tempvar%/"
echo %tempvar%
sqlplus /nolog @oracle_cool_backup.sql %tempvar%
call oracle_cool_backup_spool.batecho 重新启动数据库 sqlplus /nolog
--------------这里是备份好了后要启动数据库,我这不知道怎么做就是启不起来
sqlplus /nolog
conn /as sysdba
shutdown abort
startup force
pause
//oracle_cool_backup.sqlRe oracle_cool_backup.sql
Re 冷备份脚本Re 设置SQL*Plus环境变量
Re conn sys/[email protected]:1521/DB; ---------------如果是连接远程不知道这里是不是这样写 conn / as sysdba; set feedback off heading off verify off trimspool off
set pagesize 0 linesize 200Re 设置用户变量
define spoolfile = 'oracle_cool_backup_spool.bat'Re 设置备份文件存放路径
Re define pathfile='F:/app/Administrator/oradata/coldbackup/&1/'prompt spooling to &spoolfileRe 创建包含备份命令的脚本文件
spool &spoolfileRe 下面的f:/app/.../&1后必须加"/" 不然提示还是目录名
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$datafile;
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$controlfile;
select 'copy ' || member || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$logfile;
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$tempfile;
spool off;
Re 关闭数据库
shutdown immediate;
exit; //这个是执行到.sql文件自动生成的 oracle_cool_backup_spool.bat 生成拷贝命令copy F:\APP\ADMINISTRATOR\ORADATA\XING\SYSTEM01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\SYSAUX01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\UNDOTBS01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\USERS01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\CONTROL01.CTL "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\XING\CONTROL02.CTL "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO03.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO02.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO01.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\TEMP01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
我在网上查了很久试着修改了点脚本,可在测试备份本机的oracle数据库,由于还有缺陷(最后备份完启动数据库时,无法自行完成),还没远程测试备份过、现貼出来,望高手们指教。 小弟不胜感激!文笔有限,若没看懂,提出,我详说。//oracle_cool_backup.bat
echo 定义时间日期变量
set date=%date:~0,10%
echo %date:/=-%
set h=%time:~0,2%
set m=%time:~3,2%
set s=%time:~6,2%
set tempvar=%date%-%h%-%m%-%s%
md "F:/app/Administrator/oradata/coldbackup/%tempvar%/"
echo %tempvar%
sqlplus /nolog @oracle_cool_backup.sql %tempvar%
call oracle_cool_backup_spool.batecho 重新启动数据库 sqlplus /nolog
--------------这里是备份好了后要启动数据库,我这不知道怎么做就是启不起来
sqlplus /nolog
conn /as sysdba
shutdown abort
startup force
pause
//oracle_cool_backup.sqlRe oracle_cool_backup.sql
Re 冷备份脚本Re 设置SQL*Plus环境变量
Re conn sys/[email protected]:1521/DB; ---------------如果是连接远程不知道这里是不是这样写 conn / as sysdba; set feedback off heading off verify off trimspool off
set pagesize 0 linesize 200Re 设置用户变量
define spoolfile = 'oracle_cool_backup_spool.bat'Re 设置备份文件存放路径
Re define pathfile='F:/app/Administrator/oradata/coldbackup/&1/'prompt spooling to &spoolfileRe 创建包含备份命令的脚本文件
spool &spoolfileRe 下面的f:/app/.../&1后必须加"/" 不然提示还是目录名
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$datafile;
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$controlfile;
select 'copy ' || member || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$logfile;
select 'copy ' || name || ' "F:/app/Administrator/oradata/coldbackup/&1/"' from v$tempfile;
spool off;
Re 关闭数据库
shutdown immediate;
exit; //这个是执行到.sql文件自动生成的 oracle_cool_backup_spool.bat 生成拷贝命令copy F:\APP\ADMINISTRATOR\ORADATA\XING\SYSTEM01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\SYSAUX01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\UNDOTBS01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\USERS01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\CONTROL01.CTL "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\XING\CONTROL02.CTL "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO03.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO02.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\REDO01.LOG "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
copy F:\APP\ADMINISTRATOR\ORADATA\XING\TEMP01.DBF "F:/app/Administrator/oradata/coldbackup/2016/01/29-10-42-12/"
解决方案 »
- 在Red Hat 5下安装Oracle 10g时检测出现这个错误,请问是为什么?
- 连接oracle数据库
- sqlcode=4098 请问是什么 sqlcode一般怎么查错误信息啊
- 是否可以多个用户使用同一个触发器?
- pl/sql 打印输出 没显示
- SQL Server中取服务器时间的方法select getdate(), 在Oracle要中怎么实现?
- 各位帮忙? up有分!
- 我用的是WINDOWS版的ORACLE,我想求 利用命令行开始启动和关闭数据库的命令格式.
- 救急:如何将SQL Server的语句转换成Oracle的语句!?在线等待。
- 求助,这个问题谁见过,怎么解决?
- 怎么是oracle报ora4030的错误,改操作系统的什么参数
- 通过sql_id如何知道执行此sql语句的开始时间和结束时间?
-- 不建议使用 shutdown abort conn /as sysdba
shutdown abort
startup force
-- 最好换成 immediate , 这样就可以启动数据库了;conn /as sysdba
shutdown immediate ;
startup ;
还有shutdown immediate ; 这个并不行,我之前也试过 手敲 如下
还有在最后重新启动oracle的时候好像不需要在关闭数据库shutdown abort ,因为在执行oracle_cool_backup.sql时后面已经关闭过了, 目前就是启动不了数据库,没人帮忙解决我的问题的吗。