cancel
Showing results for 
Search instead for 
Did you mean: 

oracle procedure help

SOLVED
Go to solution
file system
Frequent Advisor

oracle procedure help

hi all.
thank you all help to me. I appreciate that.

I want to oracle hot backup to use oracle procedure

As below is the procedure and I want that after coping '.dbf' file, system switch logfile, controlfile and coping archive log file, sequentially.


SQL> select 'alter tablespace '|| tablespace_name ||' begin backup;'||chr(10)||
2 '!cp '||file_name ||' /oradata1/hotbackup/hot_bk'||chr(10)||
3 'alter tablespace '|| tablespace_name ||' end backup;'
4 from dba_data_files
5 where status <> 'INVALID'
6 /
alter tablespace SYSTEM begin backup;
!cp /oradata/IAEIS/system01.dbf /oradata1/hotbackup/hot_bk
alter tablespace SYSTEM end backup;

alter tablespace UNDOTBS1 begin backup;
!cp /oradata/IAEIS/undotbs01.dbf /oradata1/hotbackup/hot_bk
alter tablespace UNDOTBS1 end backup;

alter tablespace SYSAUX begin backup;
!cp /oradata/IAEIS/sysaux01.dbf /oradata1/hotbackup/hot_bk
alter tablespace SYSAUX end backup;

alter tablespace USERS begin backup;
!cp /oradata/IAEIS/users01.dbf /oradata1/hotbackup/hot_bk
alter tablespace USERS end backup;

alter tablespace AGRIEDU begin backup;
!cp /oradata/IAEIS_DATAFILE/agriedu01.dbf /oradata1/hotbackup/hot_bk
alter tablespace AGRIEDU end backup;

SQL> 'alter system switch logfile;'
SP2-0734: unknown command beginning "'alter sys..." - rest of line ignored.
SQL> 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE'
SP2-0734: unknown command beginning "'ALTER DAT..." - rest of line ignored.
SQL> !cp /oradata1/Archive /oradata1/hotbackup/hot_bk/Archivelog'

but
two commands are issuing "unkwon command'
'alter system switch logfile;'
'ALTER DATABASE BACKUP CONTROLFILE TO TRACE'
why it echos error like that?
how can I run the script to execute without any errors

4 conditions are needed.

1.Coping *.dbf file
2.system switch logfile
3.controlfile to trace
4.Coping archive log file.

Any helps are available. Thank you in advance.
5 REPLIES
Oviwan
Honored Contributor
Solution

Re: oracle procedure help

Hy

Why have these two commands quotes?

Regards
Frank de Vries
Respected Contributor

Re: oracle procedure help

Hi,

You need to eradicat the opening and closing quote on the command;

and end up with a line like this

SQL>alter system switch logfile;

same for alter db backup cntrfile cmd,
no quotes.




Look before you leap
Peter Godron
Honored Contributor

Re: oracle procedure help

Hi,
as you are at the SQL prompt you dshould not have quotes around your statement.
See your first part of your post:
select 'alter tablespace ...

Are you running this as script. I suspect your formatting is not correct.
file system
Frequent Advisor

Re: oracle procedure help

Thank you all .
I need to delete quotes, and run the script.
Yogeeraj_1
Honored Contributor

Re: oracle procedure help

hi,

If you are running Oracle 8i or higher, please consider moving to RMAN for backup.

It is much efficient and prevents you from making errors and also forgetting important datafiles from being backed up.

Hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)