Oracle数据库热备份SQL脚本(for Linux)

原创 2013年12月01日 21:43:15

Oracle数据库热备份脚本(for Linux)

Remark ----------------------------------------------------------------------------------
Remark filename:    user_online_backup_linux.sql
Remark description: User management online backup for linux (Linux下用户管理的在线备份)
Remark ----------------------------------------------------------------------------------

Prompt ****** Connect to database as sysdba
Remark connect / as sysdba;
Prompt sys/****** as sysdba;
connect sys/****** as sysdba;

archive log list;


Remark Set SQL*Plus variables to manipulate output
set termout on
set trimspool on
set echo off
set feedback off
set heading off
set verify off
set pagesize 0
set linesize 200
set serveroutput on

Remark set environment variable
Remark ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
Remark ORACLE_HOME==$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
Remark ORACLE_SID=ORATEST; export ORACLE_SID

Remark Set SQL*Plus variables used in this script
define dbdir = '/u01/app/oracle/oradata/ORATEST'
define archivedir = '&dbdir/archive'

define backupdir = '/u01/app/oracle/oradata/ORATEST/backup/online'
define archivebackupdir   = '&backupdir/archive'
define cpfile = '&backupdir/backup_filelist.sql'

-- archive log
alter system archive log current;

Prompt
Prompt ****** Spooling cp commands to &cpfile
Remark Create a command file with file backup commands
spool &cpfile;

declare
  cursor cur_ts is
  select *
    from dba_tablespaces
   where status <> 'READ ONLY'
     and contents <> 'TEMPORARY';

  cursor cur_df(ts_name varchar2) is
  select *
    from dba_data_files
   where tablespace_name = ts_name;
begin
  for vcur_ts in cur_ts loop
    dbms_output.put_line('alter tablespace '||vcur_ts.tablespace_name||' begin backup;');

    for vcur_df in cur_df(vcur_ts.tablespace_name) loop
      dbms_output.put_line('host cp -p '''||vcur_df.file_name||''' &backupdir/');
    end loop;

    dbms_output.put_line('alter tablespace '||vcur_ts.tablespace_name||' end backup;');
  end loop;

  dbms_output.put_line('alter database backup controlfile to trace as ''&backupdir/controlfile.trace'';');
  dbms_output.put_line('alter database backup controlfile to ''&backupdir/controlfile.binary'';');
  dbms_output.put_line('create pfile=''&backupdir/spfile_backup.ora'';');
end;
/
spool off;

set feedback on

Prompt
Prompt ****** Copy file to &backupdir
Remark Run the copy file commands from the operating system
@&cpfile;

-- archive log
alter system archive log current;

Prompt
Prompt ****** Copy archive log file to &archivebackupdir
host cp -p &archivedir/* &archivebackupdir/;

Prompt
Prompt ****** Backup Complete
exit;

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Linux下备份oracle数据库shell脚本

#!/bin/bash#获取linux服务器上/dev/sda7的磁盘空间大小DISK_SIZE=`df -k|grep /dev/sda7 |awk '{ print substr($5,1,2) ...

linux自动备份oracle数据库并上传到备份服务器 脚本实现

Linux Oracle 数据库备份脚本 ------------------------------------------------- !/bin/sh ####################...

ORACLE数据库备份用bat脚本

  • 2010年03月25日 14:07
  • 473B
  • 下载

Oracle数据库备份脚本

  • 2015年05月26日 10:13
  • 480B
  • 下载

Windows环境下Oracle数据库的自动备份脚本

@echo off echo ================================================ echo Windows环境下Oracle数据库的自动备份脚本 e...

Oracle数据库RMAN的自动备份脚本

  • 2012年07月24日 11:31
  • 35KB
  • 下载

(转)Oracle数据库的自动导出备份脚本(windows环境)

(转)Oracle数据库的自动导出备份脚本(windows环境)
  • educast
  • educast
  • 2011年04月21日 14:42
  • 1098
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle数据库热备份SQL脚本(for Linux)
举报原因:
原因补充:

(最多只允许输入30个字)