86 lines
3.1 KiB
SQL
86 lines
3.1 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : https://oracle-base.com/dba/script_creation/backup.sql
|
|
-- Author : Tim Hall
|
|
-- Description : Creates a very basic hot-backup script. A useful starting point.
|
|
-- Call Syntax : @backup
|
|
-- Last Modified: 28/01/2001
|
|
-- -----------------------------------------------------------------------------------
|
|
SET SERVEROUTPUT ON
|
|
SET LINESIZE 1000
|
|
SET TRIMOUT ON
|
|
SET FEEDBACK OFF
|
|
SPOOL Backup.txt
|
|
|
|
DECLARE
|
|
|
|
CURSOR c_tablespace IS
|
|
SELECT a.tablespace_name
|
|
FROM dba_tablespaces a
|
|
ORDER BY 1;
|
|
|
|
CURSOR c_datafiles (in_ts_name IN VARCHAR2) IS
|
|
SELECT a.file_name
|
|
FROM dba_data_files a
|
|
WHERE a.tablespace_name = in_ts_name
|
|
ORDER BY 1;
|
|
|
|
CURSOR c_archive_redo IS
|
|
SELECT a.value
|
|
FROM v$parameter a
|
|
WHERE a.name = \'log_archive_dest\';
|
|
|
|
v_sid VARCHAR2(100) := \'ORCL\';
|
|
v_backup_com VARCHAR2(100) := \'!ocopy \';
|
|
v_remove_com VARCHAR2(100) := \'!rm\';
|
|
v_dest_loc VARCHAR2(100) := \'/opt/oracleddds/dbs1/oradata/ddds/\';
|
|
|
|
BEGIN
|
|
|
|
DBMS_Output.Disable;
|
|
DBMS_Output.Enable(1000000);
|
|
|
|
DBMS_Output.Put_Line(\'svrmgrl\');
|
|
DBMS_Output.Put_Line(\'connect internal\');
|
|
|
|
DBMS_Output.Put_Line(\' \');
|
|
DBMS_Output.Put_Line(\'-- ----------------------\');
|
|
DBMS_Output.Put_Line(\'-- Backup all tablespaces\');
|
|
DBMS_Output.Put_Line(\'-- ----------------------\');
|
|
FOR cur_ts IN c_tablespace LOOP
|
|
DBMS_Output.Put_Line(\' \');
|
|
DBMS_Output.Put_Line(\'ALTER TABLESPACE \' || cur_ts.tablespace_name || \' BEGIN BACKUP;\');
|
|
FOR cur_df IN c_datafiles (in_ts_name => cur_ts.tablespace_name) LOOP
|
|
DBMS_Output.Put_Line(v_backup_com || \' \' || cur_df.file_name || \' \' ||
|
|
v_dest_loc || SUBSTR(cur_df.file_name, INSTR(cur_df.file_name, \'/\', -1)+1));
|
|
END LOOP;
|
|
DBMS_Output.Put_Line(\'ALTER TABLESPACE \' || cur_ts.tablespace_name || \' END BACKUP;\');
|
|
END LOOP;
|
|
|
|
DBMS_Output.Put_Line(\' \');
|
|
DBMS_Output.Put_Line(\'-- -----------------------------\');
|
|
DBMS_Output.Put_Line(\'-- Backup the archived redo logs\');
|
|
DBMS_Output.Put_Line(\'-- -----------------------------\');
|
|
FOR cur_ar IN c_archive_redo LOOP
|
|
DBMS_Output.Put_Line(v_backup_com || \' \' || cur_ar.value || \'/* \' ||
|
|
v_dest_loc);
|
|
END LOOP;
|
|
|
|
|
|
DBMS_Output.Put_Line(\' \');
|
|
DBMS_Output.Put_Line(\'-- ----------------------\');
|
|
DBMS_Output.Put_Line(\'-- Backup the controlfile\');
|
|
DBMS_Output.Put_Line(\'-- ----------------------\');
|
|
DBMS_Output.Put_Line(\'ALTER DATABASE BACKUP CONTROLFILE TO \'\'\' || v_dest_loc || v_sid || \'Controlfile.backup\'\';\');
|
|
DBMS_Output.Put_Line(v_backup_com || \' \' || v_dest_loc || v_sid || \'Controlfile.backup\');
|
|
DBMS_Output.Put_Line(v_remove_com || \' \' || v_dest_loc || v_sid || \'Controlfile.backup\');
|
|
|
|
DBMS_Output.Put_Line(\' \');
|
|
DBMS_Output.Put_Line(\'EXIT\');
|
|
|
|
END;
|
|
/
|
|
|
|
PROMPT
|
|
SPOOL OFF
|
|
SET LINESIZE 80
|
|
SET FEEDBACK ON |