ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Example for backup tablespace

Example for backup tablespace

原创 Linux操作系统 作者:licheng79 时间:2011-09-09 10:48:07 0 删除 编辑

In this case, we use networker to online backup a tablespace. For transportable tablespace, we can directly backup and copy among platform. and databases. For other tablespace, we should change the mode to backup. Following example shows a PL/SQL procedure to realize the functionalities.

 

begin

  dbms_output.enable(32000);

  dbms_output.put_line('set echo on;');

  dbms_output.put_line('set time on;');

  dbms_output.put_line('alter system archive log current;');

  if cur1%ISOPEN

  then

     close cur1;

  end if;

 

  open cur1;

 

  fetch cur1 into tname,fname;

 

  tname1 := tname;

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

 

  while cur1%FOUND loop

 

        if tname1 != tname then

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

           dbms_output.put_line('alter system switch logfile;');

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

           tname1 := tname;

        end if;

 

           dbms_output.put_line('!/usr/bin/save -s ctmedm01.ctm.plc.cwintra.com -w "14 days" -y "14 days" -b VTLWeeklyPool '||fname);

 

        fetch cur1 into tname,fname;

 

  end loop;

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

 

  close cur1;

  dbms_output.put_line('alter system archive log current;');

  dbms_output.put_line('alter system switch logfile;');

  dbms_output.put_line('alter system switch logfile;');

  dbms_output.put_line('!/usr/bin/save -s ctmedm01.ctm.plc.cwintra.com -w "14 days" -y "14 days" -b VTLWeeklyPool /disk2/oradata/oisdb/archive');

  dbms_output.put_line('alter system archive log current;');

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

  dbms_output.put_line('alter database backup controlfile to '||''''||

                       '/disk1/admin/oisdb/udump/control.'||

                       to_char(sysdate,'YYYYMMDDHH24MISS')||''''||';');

  dbms_output.put_line('!/usr/bin/save -s ctmedm01.ctm.plc.cwintra.com -w "14 days" -y "14 days" -b VTLWeeklyPool /disk1/admin/oisdb/udump');

  dbms_output.put_line('!/usr/bin/save -s ctmedm01.ctm.plc.cwintra.com -w "14 days" -y "14 days" -b VTLWeeklyPool /disk1/orahome/dbs');

  dbms_output.put_line('!/usr/bin/save -s ctmedm01.ctm.plc.cwintra.com -w "14 days" -y "14 days" -b VTLWeeklyPool /disk1/admin/oisdb/pfile');

  dbms_output.put_line('exit;');

end;

/

Comments: Update networker SAVE retention to 14 days and backup pool to defined pool as “VTLWeeklyPool”

 

Reference:

Transportable Tablespaces

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another.

 

READ ONLY | READ WRITE

Specify READ ONLY to place the tablespace in transition read-only mode. In this state, existing transactions can complete (commit or roll back), but no further DML operations are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace. You cannot make the SYSAUX tablespace READ ONLY.

Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE ... RENAME.

 

BEGIN BACKUP

Specify BEGIN BACKUP to indicate that an open backup is to be performed on the datafiles that make up this tablespace. This clause does not prevent users from accessing the tablespace. You must use this clause before beginning an open backup.


END BACKUP

Specify END BACKUP to indicate that an online backup of the tablespace is complete. Use this clause as soon as possible after completing an online backup. Otherwise, if an instance failure or SHUTDOWN ABORT occurs, then Oracle Database assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance startup.

820_001.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-707016/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    69793