Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Backups with awk (Oracle7-style)

Backups with awk (Oracle7-style)

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Tue, 08 Feb 2005 20:40:15 GMT
Message-ID: <Pine.BSO.4.58.0502041047520.9695@bart.rhadmin.org>


I have recently rewritten my previous Korn-shell backup script with awk. It is clean(er), and addresses new(er) features. I still maintain a lot of Oracle 7, and I like my solution more than the BMC cruft that I've ripped out. I've posted the script below.

The script does not actually do the backup by default, but only prints the the required actions to the standard output (unless you invoke it with the DOIT parameter).

It can backup every database in /etc/oratab like so:

	for x in $(awk -F: "!/^[ \t]*#/ && NF==3" /etc/oratab); do 
	ORACLE_SID=$(echo $x|cut -d: -f1) ORACLE_HOME=$(echo $x|cut -d: -f2)  \
	awkback DOIT DEST=$(echo $x|cut -d: -f2)/backup; done

It can do the above on a collection of remote systems like so:

	for y in $(<hosts.list); do
	for x in $(awk -F: "!/^[ \t]*#/ && NF==3" /etc/oratab); do 
	ORACLE_SID=$(echo $x|cut -d: -f1) ORACLE_HOME=$(echo $x|cut -d: -f2)  \
	awkback DOIT DEST=$(echo $x|cut -d: -f2)/backup; done; done

It doesn't do any handling of archived logs or removal of old backups.

If anyone has any suggestions for improvements, hey, I'd love to hear from you.


   / Charles J. Fisher   | Really, I'm not out to destroy Microsoft.        /
  /  cfisher_at_rhadmin.org |  That will just be a completely unintentional   /
 /   http://rhadmin.org  |  side effect. -Linus Torvalds                  /
--------------------------------------------------------------------------


#!/usr/bin/awk -f

BEGIN {

	NOREDO = 0;
	FILEBYFILE = 0;
	DOIT = 0;
	SPFILE = 0;

	DEST = "/home/oracle/backup/";

	CONNECT = "connect / as sysdba";
	QUIET = "set pages 0 lin 32767 feedback off verify off heading off";
	SQLPLUS = ENVIRON["ORACLE_HOME"] "/bin/sqlplus -silent /nolog";
	INITORA = ENVIRON["ORACLE_HOME"] "/dbs/init"ENVIRON["ORACLE_SID"]".ora";
	UDUMP = ENVIRON["ORACLE_HOME"] "/udump";

# (Most) OS-Specific settings - unxutils.sourceforge.net is your friend
	CP = "cp";
	MV = "mv";
	COMPRESS = "bzip2 -9q";
	CMDINIT = SQLPLUS "<<EOF\n" CONNECT "\n" QUIET "\n";


	for(i = 1; i < ARGC; i++)

{
if(ARGV[i] == "HELP") Usage(); if(ARGV[i] == "NOREDO") NOREDO = 1; if(ARGV[i] == "FILEBYFILE") FILEBYFILE = 1; if(ARGV[i] == "DOIT") DOIT = 1; if(ARGV[i] == "SPFILE") SPFILE = 1; if(substr(ARGV[i],1,5) == "DEST=") DEST = xeq(ARGV[i]); if(substr(ARGV[i],1,8) == "CONNECT=") CONNECT = xeq(ARGV[i]); if(substr(ARGV[i],1,6) == "QUIET=") QUIET = xeq(ARGV[i]); if(substr(ARGV[i],1,8) == "SQLPLUS=") SQLPLUS = xeq(ARGV[i]); if(substr(ARGV[i],1,8) == "INITORA=") INITORA = xeq(ARGV[i]); if(substr(ARGV[i],1,6) == "UDUMP=") UDUMP = xeq(ARGV[i]); if(substr(ARGV[i],1,8) == "CP=") CP = xeq(ARGV[i]); if(substr(ARGV[i],1,8) == "MV=") MV = xeq(ARGV[i]); if(substr(ARGV[i],1,9) == "COMPRESS=") COMPRESS = xeq(ARGV[i]); if(substr(ARGV[i],1,9) == "LGMNRDCT=") LGMNRDCT = xeq(ARGV[i]); } LATEST_TRACE = "cd " UDUMP \ ";cp $(ls -t $(fgrep -l 'CREATE CONTROLFILE' *.trc)|head -1) " \ DEST "\n"; script = ""; pfile_backup(); if(LGMNRDCT) logminerdict(LGMNRDCT); bin_controlfile_backup(); trace_controlfile_backup(); build_df_array(); if(FILEBYFILE) file_by_file_backup(); else whole_tablespace_backup(); if(!NOREDO) hot_copy_redo(); if(DOIT)
{
cmd = CMDINIT script "\nEOF\n"; while(cmd | getline) print; } else print script;

}

function deleq(x)
{

        return substr(x, 1 + index(x, "="));
}

function pfile_backup()
{

	if(SPFILE)
		script = script "CREATE PFILE='" DEST "init" \
			ENVIRON["ORACLE_SID"] ".ora' FROM SPFILE;\n"
	else
		script = script "!" CP " " INITORA " " DEST "\n"
}

function logminerdict(dpath)
{

	dname = basename(dpath)
	ddir  = dirname(dpath)

	script = script "begin\nsys.dbms_logmnr_d.build('" dname "', '" \
		ddir "');\nend;\n/\n!" CP " " dpath " " DEST "\n" \
		"!" COMPRESS " " DEST dname "\n";

}

function bin_controlfile_backup()
{

	script = script "alter database backup controlfile to '" DEST \
		"CONTROL.BAK';\n";

	script = script "!" COMPRESS " " DEST "CONTROL.BAK\n";
}

function trace_controlfile_backup()
{

	script = script "alter database backup controlfile to trace;\n";
	script = script "!" LATEST_TRACE;

}

function build_df_array( cmd)
{

	cmd = CMDINIT \
	"select '+'||file_name,tablespace_name from dba_data_files;\nEOF\n";

	while(cmd | getline)
		if(substr($0, 1, 1) == "+" ) file[substr($1, 2)] = $2;

	close(cmd);

}

function whole_tablespace_backup( x, y)
{

        for(x in file) tablespace[file[x]] = 1;

	for(x in tablespace)

{
script = script "alter tablespace " x " begin backup;\n"; for(y in file) if(file[y] == x) script = script "!" CP " " y " " DEST "\n"; script = script "alter tablespace " x " end backup;\n"; for(y in file) if(file[y] == x) script=script"!"COMPRESS" "DEST basename(y)"\n"; }

}

function file_by_file_backup( x)
{

	for(x in file)

{
script = script "alter tablespace " file[x] " begin backup;\n"; script = script "!" CP " " x " " DEST "\n"; script = script "alter tablespace " file[x] " end backup;\n"; script = script"!"COMPRESS" "DEST basename(x)"\n"; }

}

function hot_copy_redo( x, cmd, logs)
{

	cmd = CMDINIT \
		"SELECT '+'|| RTRIM(x.member) FROM v\\\$logfile x " \
		"WHERE x.member IN ( SELECT member FROM v\\\$logfile WHERE " \
		"group# = x.group# AND rownum < 2 );\nEOF\n";

	while(cmd | getline)
		if(substr($0, 1, 1) == "+")
			logs[substr($1, 2)] = 1;

	script = script "!" CP " ";

	for(x in logs)
		script = script x " ";

	script = script DEST "\n";

	for(x in logs)
		script = script "!" MV " " DEST basename(x) " " \
			DEST "do-not-use-" basename(x) "\n" \
			"!" COMPRESS " " DEST "do-not-use-" basename(x) "\n";
}

function basename(x)
{

	gsub(/.*\//, "", x);
	gsub(/.*\\/, "", x);
	return x;

}

function dirname(x)
{

	gsub(/[\\\/][^\\\/]*$/, "", x);
	return x;

}

function p(x)
{

        print x;
}

function Usage()
{

	p("\nawkback - Oracle7-style hot backup of an online database.\n");
	p("Options:");
	p("\tNOREDO - Do not copy online redo (not Oracle-supported).");
	p("\tFILEBYFILE - Don't copy a whole tablespace at once (disk space).");
	p("\tDOIT - Don't just print out the script, run it.");
	p("\tSPFILE - Ignore any init.ora, dump binary spfile.");
	p("\tINITORA - Path to init.ora (can include addl files).");
	p("\tDEST - Destination directory; include trailing slash.");
	p("\tCONNECT= - Oracle account logon (connect internal).");
	p("\tSQLPLUS= - Path to sqlplus, with command line args.");
	p("\tQUIET= - sqlplus directive for terse output.");
	p("\tBDUMP= - Directory to find controlfile trace.");
	p("\tLGMNRDCT= - Create dictionary in utl_file_dir, then backup.");
	p("\tCP - OS copy command.");
	p("\tMV - OS move command.");
	p("\nExample:");
	p("\tORACLE_SID=orcl ORACLE_HOME=/oracle awkback SPFILE DOIT");
	exit;

} Received on Tue Feb 08 2005 - 14:40:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US