Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

How to take the Online Fulldb backup using Backup Script in Oracle 11gR2

Posted by Mir Sayeed Hassan on February 4th, 2019

How to take the Online Fulldb backup using Backup Script in Oracle 11gR2

Database Status

sys@TESTDB> select instance_name,status,version from v$instance;

INSTANCE_NAME    STATUS       VERSION
---------------- ------------ -----------------
testdb           OPEN         11.2.0.4.0

Create  the script in below location

[oracle@testdb ~]$ cd /home/oracle/scripts/
[oracle@testdb scripts]$ vi fulldb_bkp_script.sql
set serveroutput on
set trimspool on
set line 500
set head off
set feed off
spool fulldb_bkp_script.cmd
declare
  copy_command constant varchar2(30) := 'cp';
  copy_destination constant varchar2(30) := '/backup/db_backup'; --Backup files directory location
  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_$database;
  if logmode <> 'ARCHIVELOG' then
    raise_application_error(-20000, 'ERROR: Database must be in ARCHIVELOG mode!!!');
    return;
  end if;
  dbms_output.put_line('spool backup.'||dbname||'.'|| to_char(sysdate, 'ddMonyy')||'.log');
  --Backup tablespaces
  for c1 in (select tablespace_name ts from sys.dba_tablespaces where CONTENTS <;>; 'TEMPORARY')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    --Backup datafiles
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
      loop
        dbms_output.put_line('!'||copy_command||' '||c2.fil||' '||copy_destination);
      end loop;
      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
    end loop;
  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
  copy_destination||'control.'||dbname||'.'||
  to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/
spool off
set head on
set feed on
set serveroutput off

Run the fulldb_bkp_script using sqlplus

[oracle@testdb scripts]$ !sq
sqlplus
sys@TESTDB> @/home/oracle/scripts/fulldb_bkp_script.sql;
spool backup.TESTDB.04Feb19.log
alter tablespace SYSTEM begin backup;
!cp /u01/app/oracle/oradata/prim/system01.dbf /backup/db_backup
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup;
!cp /u01/app/oracle/oradata/prim/sysaux01.dbf /backup/db_backup
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 begin backup;
!cp /u01/app/oracle/oradata/prim/undotbs.dbf /backup/db_backup
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup;
!cp /u01/app/oracle/oradata/prim/users01.dbf /backup/db_backup
alter tablespace USERS end backup;
alter tablespace EXAMPLE begin backup;
!cp /u01/app/oracle/oradata/prim/example01.dbf /backup/db_backup
alter tablespace EXAMPLE end backup;
alter tablespace GOLDENGATE begin backup;
!cp /u01/app/oracle/oradata/prim/goldengate01.dbf /backup/db_backup
alter tablespace GOLDENGATE end backup;
alter database backup controlfile to trace;
alter database backup controlfile to '/backup/db_backupcontrol.TESTDB.04Feb191151';
alter system switch logfile;
spool off

sys@TESTDB> exit

After running this scripts the .cmd file get generate to performing the backup of full database

Verify

[oracle@testdb scripts]$ ls
fulldb_bkp_script.cmd fulldb_bkp_script.sql

Now run the .cmd file for backup of full database using sqlplus

[oracle@testdb scripts]$ !sq
sqlplus

sys@TESTDB> @/home/oracle/scripts/fulldb_bkp_script.cmd;
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Database altered.
Database altered.
System altered.

Verify the Backup of database in given location:

[oracle@testdb ~]$ cd /backup/fulldb_backup/

[oracle@testdb db_backup]$ ls
system01.dbf sysaux01.dbf undotbs.dbf  users01.dbf example01.dbf goldengate01.dbf

============Hence tested & verified in our test env=============