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