| Using Operating System commands within an SQL Script. [message #51062] |
Tue, 23 April 2002 06:39  |
Andrew Ford
Messages: 18
Registered: November 2001
|
Junior Member |
|
|
Can anyone help?
Is there any way that you can issue unix os commands within a SQL script?
I am trying to write a backup sql script that will shutdown the database, copy certain physical database files to backup location and then start the database up again.
|
|
|
|
|
|
| Re: Using Operating System commands within an SQL Script. [message #51066 is a reply to message #51062] |
Tue, 23 April 2002 07:27  |
Kishore
Messages: 45
Registered: September 2000
|
Member |
|
|
Hi,
Type 'host' command at the SQL prompt. This will take you to operating system command prompt. Once you complete your work here type 'exit'. The prompt will come back to SQL.
SQL>host
c:> -- perform your os activity -- once it done
c:>exit
SQL>
I hope this helps you,
Kishore
|
|
|
|
|
|
| Re: Using Operating System commands within an SQL Script. [message #51075 is a reply to message #51062] |
Tue, 23 April 2002 08:52 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Here is a script that was generated by an old program I used to have. You will get the idea. It was generated on NT so just change the host command to !. I generated it a while back connected to a DB on solaris so the dirs won't match but this will get you started.
doc
*********************************************************************
* Backup Script Created by EZSQL *
* and SYS *
* for Database DEV *
* *
* The script will perform the following tasks : *
* *
* 1. Set individual tablespace into backup mode. *
* 2. Copy the datafile(s) to a backup directory. *
* 3. Take the individual tablespace out of backup mode. *
* 4. Optional - Create export parameter file. *
* 5. Optional - Perform a full export. *
* 6. Optional - Create import parameter file. *
* 7. Copy listener.ora file to backup directory. *
* 8. Copy init.ora file to backup directory. *
* 9. Optional - Copy all Files in Backup Directory to 2nd Disk *
* 10. Optional - Copy all Files to tape using NTBACKUP command *
* (overwrites all data on tape) *
* *
*********************************************************************
#
set feedback off
set verify off
set heading off
-- First make backup directory if it does not exist.
-- Then delete old backup files if they do exist.
host MD C:Oracle_backup
host DEL C:Oracle_backup*.*
-- Backup Data Files
-- Put Each tablespace in backup mode,
-- Copy then file to backup directory,
-- Take tablespace out of backup mode
Alter tablespace USERS begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/users01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/users01.dbf C:Oracle_backup
Alter tablespace USERS end backup;
Alter tablespace TOOLS begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/tools01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/tools01.dbf C:Oracle_backup
Alter tablespace TOOLS end backup;
Alter tablespace TEMP begin backup;
host echo copying datafile /oradb/kham/u3/oradata/dev/temp01.dbf to C:Oracle_backup
host copy /oradb/kham/u3/oradata/dev/temp01.dbf C:Oracle_backup
Alter tablespace TEMP end backup;
Alter tablespace SYSTEM begin backup;
host echo copying datafile /oradb/kham/u3/oradata/dev/system01.dbf to C:Oracle_backup
host copy /oradb/kham/u3/oradata/dev/system01.dbf C:Oracle_backup
Alter tablespace SYSTEM end backup;
Alter tablespace SYIDX begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/SYIDX01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/SYIDX01.dbf C:Oracle_backup
Alter tablespace SYIDX end backup;
Alter tablespace SYDAT begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/SYDAT01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/SYDAT01.dbf C:Oracle_backup
Alter tablespace SYDAT end backup;
Alter tablespace SFIDX begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/SFIDX01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/SFIDX01.dbf C:Oracle_backup
Alter tablespace SFIDX end backup;
Alter tablespace SFDAT begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/SFDAT01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/SFDAT01.dbf C:Oracle_backup
Alter tablespace SFDAT end backup;
Alter tablespace SDIDX begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/SDIDX01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/SDIDX01.dbf C:Oracle_backup
Alter tablespace SDIDX end backup;
Alter tablespace SDDAT begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/SDDAT01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/SDDAT01.dbf C:Oracle_backup
Alter tablespace SDDAT end backup;
Alter tablespace RBS begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/rbs01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/rbs01.dbf C:Oracle_backup
Alter tablespace RBS end backup;
Alter tablespace PAIDX begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/PAIDX01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/PAIDX01.dbf C:Oracle_backup
Alter tablespace PAIDX end backup;
Alter tablespace PADAT begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/PADAT01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/PADAT01.dbf C:Oracle_backup
Alter tablespace PADAT end backup;
Alter tablespace CTIDX begin backup;
host echo copying datafile /oradb/kham/u2/oradata/dev/CTIDX01.dbf to C:Oracle_backup
host copy /oradb/kham/u2/oradata/dev/CTIDX01.dbf C:Oracle_backup
Alter tablespace CTIDX end backup;
Alter tablespace CTDAT begin backup;
host echo copying datafile /oradb/kham/u4/oradata/dev/CTDAT01.dbf to C:Oracle_backup
host copy /oradb/kham/u4/oradata/dev/CTDAT01.dbf C:Oracle_backup
Alter tablespace CTDAT end backup;
-- Force a log switch
alter system switch logfile;
--Wait 1 minute for Export to complete...
exec dbms_lock.sleep(60);
-- Back up the archive log files, then delete them
-- from the archive directory. Note : ALL FILES IN THE
-- ARCHIVE DIRECTORY ARE DELETED!
host echo copying archive files to C:Oracle_backup
host copy C:oracleora81DatabaseArchive*.* C:Oracle_backup
host echo deleting old archive files...
host DEL C:oracleora81DatabaseArchive*.*
-- Make Backup Control File in backup directory
host echo backing up control file to C:Oracle_backupControl.Ctl
alter database backup controlfile to 'C:Oracle_backupControl.Ctl';
-- Backup Control File in trace file
host echo backing up control file to trace
alter database backup controlfile to trace;
-- Back up Online REDO log files. Most likely these will not be used, but you should have them backed up anyway.
host echo backing up redo log file /oradb/kham/u3/oradata/dev/redo01.log to C:Oracle_backup
host copy /oradb/kham/u3/oradata/dev/redo01.log C:Oracle_backup
host echo backing up redo log file /oradb/kham/u3/oradata/dev/redo02.log to C:Oracle_backup
host copy /oradb/kham/u3/oradata/dev/redo02.log C:Oracle_backup
host echo backing up redo log file /oradb/kham/u3/oradata/dev/redo03.log to C:Oracle_backup
host copy /oradb/kham/u3/oradata/dev/redo03.log C:Oracle_backup
-- Backup Network configuration files
host copy C:oracleora81NetworkAdmin*.* C:Oracle_backup
-- Backup INIT.ORA file
host copy C:oracleora81DatabaseInitORCL.ORA C:Oracle_backup
--Perform Full Database Export
host EXP parfile=C:holdSQLdev_backup_EXP.Par
--Wait 5 minutes for Export to complete...
exec dbms_lock.sleep(300);
--Copy Files to 2nd backup directory
host xcopy C:Oracle_backup*.* Z: /f /i
host ntbackup backup C:Oracle_backup /d "Oracle Backup" /b /t normal
Exit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
