Mir Sayeed Hassan – Oracle Blog

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=============