手工热备份脚本

手工冷备份脚本：http://blog.csdn.net/rlhua/article/details/11850445

 RMAN脚本：http://blog.csdn.net/rlhua/article/details/12714647

 

热备的步骤：

alter database/tablespace XXX begin backup

cp files

alter database/tablespace XXX end backup

执行命令后，会在数据块拷贝完后写入新的scn号。

 

 

首先新建两个目录，分别为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

 

1、查看有哪些表空间，临时表空间不用备份。

sys@TEST0910> select tablespace_name,contents,status from dba_tablespaces;

SYSTEM                         PERMANENT ONLINE

SYSAUX                         PERMANENT ONLINE

UNDOTBS1                       UNDO      ONLINE

TEMP                           TEMPORARY ONLINE

USERS                          PERMANENT ONLINE

EXAMPLE                        PERMANENT ONLINE

TESTTBS                        PERMANENT ONLINE

sys@TEST0910> select tablespace_name from dba_tablespaces where contents not like 'TEMP%'

  2  and status='ONLINE';

SYSTEM

SYSAUX

UNDOTBS1

USERS

EXAMPLE

TESTTBS

2、查看某个表空间下有哪些数据文件。

sys@TEST0910> select file_name from dba_data_files where tablespace_name='USERS';

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

3、编写存储过程，备份数据文件和控制文件，转储到新的位置。

sys@TEST0910> declare

  2          cursor cu_tablespace is

                select tablespace_name from dba_tablespaces

                where contents not like 'TEMP%'  and status='ONLINE';

        cursor cu_datafile(name varchar2) is

                select file_name from dba_data_files where tablespace_name=name;

  7  begin

        for i in cu_tablespace loop

                dbms_output.put_line('alter tablespace '|| i.tablespace_name||' begin backup;');

10                  for j in cu_datafile(i.tablespace_name) loop

                        dbms_output.put_line('host cp '||j.file_name||' &bakdir');

                end loop;

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

14          end loop;

        dbms_output.put_line('alter database backup controlfile to trace;');

        dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.ctl'';');

end;

18  /

alter tablespace SYSTEM begin backup;

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

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

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

alter tablespace SYSAUX end backup;

alter tablespace UNDOTBS1 begin backup;

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

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;

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

alter tablespace USERS end backup;

alter tablespace EXAMPLE begin backup;

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

alter tablespace EXAMPLE end backup;

alter tablespace TESTTBS begin backup;

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

alter tablespace TESTTBS end backup;

alter database backup controlfile to trace;

alter database backup controlfile to '/u01/app/oracle/bak/hot_bak/control01.ctl';

• 热备脚本：

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

set pagesize 0 linesize 200

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

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

set serveroutput on

spool &bakscp

prompt alter system switch logfile;;

declare

        cursor cu_tablespace is

                select tablespace_name from dba_tablespaces

                where contents not like 'TEMP%'  and status='ONLINE';

        cursor cu_datafile(name varchar2) is

                select file_name from dba_data_files where tablespace_name=name;

begin

        for i in cu_tablespace loop

                dbms_output.put_line('alter tablespace '|| i.tablespace_name||' begin backup;');

                for j in cu_datafile(i.tablespace_name) loop

                        dbms_output.put_line('host cp '||j.file_name||' &bakdir');

                end loop;

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

        end loop;

        dbms_output.put_line('alter database backup controlfile to trace;');

        dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.ctl'';');

end;

/

spool off

@&bakscp

• 执行脚本：

sys@TEST0910> @/u01/app/oracle/bak/hot_bak.sql

alter system switch logfile;

alter tablespace SYSTEM begin backup;

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

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

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

alter tablespace SYSAUX end backup;

alter tablespace UNDOTBS1 begin backup;

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

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;

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

alter tablespace USERS end backup;

alter tablespace EXAMPLE begin backup;

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

alter tablespace EXAMPLE end backup;

alter tablespace TESTTBS begin backup;

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

alter tablespace TESTTBS end backup;

alter database backup controlfile to trace;

alter database backup controlfile to '/u01/app/oracle/bak/hot_bak/control01.ctl';

 

 

 

 

系统位置查看：

[oracle@rtest bak]$ more hot_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/hot_bak'

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

set serveroutput on

spool &bakscp

prompt alter system switch logfile;;

declare

        cursor cu_tablespace is

                select tablespace_name from dba_tablespaces

                where contents not like 'TEMP%'  and status='ONLINE';

        cursor cu_datafile(name varchar2) is

                select file_name from dba_data_files where tablespace_name=name;

begin

        for i in cu_tablespace loop

                dbms_output.put_line('alter tablespace '|| i.tablespace_name||' begin backup;');

                for j in cu_datafile(i.tablespace_name) loop

                        dbms_output.put_line('host cp '||j.file_name||' &bakdir');

                end loop;

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

        end loop;

        dbms_output.put_line('alter database backup controlfile to trace;');

        dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.ctl'';');

end;

/

spool off

@&bakscp

[oracle@rtest bak]$ more hot_cmd.sql

alter system switch logfile;

alter tablespace SYSTEM begin backup;                                                                               

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

alter tablespace SYSTEM end backup;                                                                                 

alter tablespace SYSAUX begin backup;                                                                               

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

alter tablespace SYSAUX end backup;                                                                                 

alter tablespace UNDOTBS1 begin backup;                                                                             

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

alter tablespace UNDOTBS1 end backup;                                                                               

alter tablespace USERS begin backup;                                                                                

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

alter tablespace USERS end backup;                                                                                  

alter tablespace EXAMPLE begin backup;                                                                              

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

alter tablespace EXAMPLE end backup;                                                                                

alter tablespace TESTTBS begin backup;                                                                              

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

alter tablespace TESTTBS end backup;                                                                                

alter database backup controlfile to trace;                                                                         

alter database backup controlfile to '/u01/app/oracle/bak/hot_bak/control01.ctl';                                   

[oracle@rtest bak]$ ls

cold_bak  cold_bak.sql  cold_cmd.sql  hot_bak  hot_bak.sql  hot_cmd.sql

[oracle@rtest bak]$ cd hot_bak

[oracle@rtest hot_bak]$ ls

control01.ctl  example01.dbf  sysaux01.dbf  system01.dbf  testtb.dbf  undotbs01.dbf  users01.dbf