Hot backup script fails to execute

All posts relating to Oracle database administration.

Moderator: Tim...

Hot backup script fails to execute

Postby sridhartempalle » Wed Jul 17, 2013 7:55 pm

Hello Tim,

I am trying to take hot backup of whole database using a shell script. The script 'hot_backup_script.sql' consists:

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL hot_backup.sql
SELECT 'spool hot.log' FROM dual;
SELECT 'alter database begin backup;' FROM dual;
SELECT '!cp '||file_name||' /u01/bkup/scpbkup' FROM dual;
SELECT 'alter database '||database_name||' end backup;' FROM dual;
SELECT 'alter database backup controlfile to '||'''/u01/bkup/scpbkup/control.new'''||';' FROM dual;
SELECT 'alter system switch logfile;' FROM dual;
SPOOL off
@hot_backup.sql
EXIT

It throws an error which reads as following:

SQL> @/u01/bkup/hot_backup_script.sql

spool hot.log

alter database ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM begin backup;
SELECT '!cp '||file_name||' /u01/bkup/scpbkup' FROM dual
*
ERROR at line 1:
ORA-00904: "FILE_NAME": invalid identifier

alter database ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM end backup;

alter database backup controlfile to '/u01/bkup/scpbkup/control.new';

alter system switch logfile;
alter database ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM begin backup
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

alter database ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM end backup
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

alter database backup controlfile to '/u01/bkup/scpbkup/control.new'
*
ERROR at line 1:
ORA-01580: error creating control backup file /u01/bkup/scpbkup/control.n ew
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1 .0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$
------------------End of error message-----------------------

I am a newbie at scripting and am unable to trace the mistake that am committing. Can you please correct the mistakes in my script. Thank you.
sridhartempalle
Member
 
Posts: 41
Joined: Mon May 06, 2013 7:13 pm

Re: Hot backup script fails to execute

Postby Tim... » Wed Jul 17, 2013 9:38 pm

Hi.

You are selecting from dual each time, when I think you mean to select from a dictionary view in some cases. For example,

Code: Select all
-- This
SELECT '!cp '||file_name||' /u01/bkup/scpbkup' FROM dual;

--Should probably be this.
SELECT '!cp '||file_name||' /u01/bkup/scpbkup' FROM dba_data_files;


And,

Code: Select all
SELECT 'alter database '||database_name||' end backup;' FROM dual;

to

SELECT 'alter database end backup;' FROM dual;


Also, the following can be simplified,

Code: Select all
SELECT 'alter database backup controlfile to '||'''/u01/bkup/scpbkup/control.new'''||';' FROM dual;

to

SELECT 'alter database backup controlfile to''/u01/bkup/scpbkup/control.new'';' FROM dual;


Note. This is not the best way to backup Oracle databases now. It is better to use RMAN.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Hot backup script fails to execute

Postby sridhartempalle » Thu Jul 25, 2013 6:48 pm

Hello Tim,

Thanks a lot for the script help. Though it wasn't the best practice, it helped me learn them. My problem is thus solved with ur assistance.

Thanks a lot.
sridhartempalle
Member
 
Posts: 41
Joined: Mon May 06, 2013 7:13 pm

Re: Hot backup script fails to execute

Postby Tim... » Thu Jul 25, 2013 8:35 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 8 guests

cron