Shulou(Shulou.com)06/01 Report--

It took more than a day to come up with a SQL script to perform an automatic cold backup of ORACLE. The record is as follows:

Set echo off-turns off the echo of the command, but displays the executed command. Set serveroutput on-returns the result. If closed, the result cannot be transferred to the next file. Set escape on-- turn on the escape character function, otherwise encounter / cannot proceed. Set heading off-- close the result header line display set line 300-- set the width of the returned result display to avoid incomplete results after the sed command crosses the lines. Ho mkdir $ORACLE_HOME/ `date +% yearly% masked% d`-create a directory to store backup files, named after the date of the day. Make sure it is the same as the directory in SQL below. Spool / tmp/bak1.sqldeclare-- declare a variable. The value used to receive the current date. V_time varchar2 (30); beginselect to_char (sysdate,'yyyy_mm_dd') into v_time-- gets the date of the day. From dual;dbms_output.put_line ('select' and'ho cp'\ |\ | name\ |\ |'$ORACLE_HOME\ /'| v_time | |''from vastly controlfiled.'); dbms_output.put_line (' select''ho cp'\ |\ | name\ |\ |'$ORACLE_HOME\ /'| v_time | | 'from' ho cp'\ |\ | member\ |\ |'$ORACLE_HOME\ /'| | v_time | | 'from v$logfile' '); dbms_output.put_line (' select 'and' ho cp'\ |\ | name\ |\ |'$ORACLE_HOME\ /'| v_time | |''from vasttempfile.'); dbms_output.put_line (' create pfile=''$ORACLE_HOME\ /'| v_time | |'\ / init$ORACLE_SID.ora'' from spfile;'); dbms_output.put_line ('ho cp $ORACLE_HOME\ / dbs\ / orapw$ORACLE_SID $ORACLE_HOME\ /'| v_time); end / spool off-after getting the date above, you get the complete SQL original statement with the date value and some result description statements. -- the following gets the pure original SQL statement used for execution. Ho sed-n'/ select/p' / tmp/bak1.sql > / tmp/bak2.sqlho sed-n'/ ^ create/p' / tmp/bak1.sql > > / tmp/bak2.sqlho sed-n'/ ^ ho cp/p;' / tmp/bak1.sql > > / tmp/bak2.sql-- is used to execute the original SQLPLUS statement and back up the parameter file and password file, as well as the result description statement. Spool / tmp/bak3.sqlstart / tmp/bak2.sqlspool offho sed-n'/ ^ ho cp/p' / tmp/bak3.sql > $ORACLE_HOME/back.sql-filter out useless result statement statements. Get the final useful SQLPLUS command. Shutdown immediate-closes the database for cold backup. Start $ORACLE_HOME/back.sqlstartupho rm / tmp/bak*.sql $ORACLE_HOME/back.sql-removes the temporarily generated execution script.

Description: it is difficult to get the current date value under 1.SQL and apply it to the directory of the SQLPLUS command.

The 2.sed command can use sed-I to simplify the generated transitional sql script.

3. It should be possible to define variables under SQLPLUS and assign values to the date of the day, and then apply them to other backup commands. It is not successful for the time being, so we will study it later.

4. This is to make a SQL script for learning, or use the system shell script to do it, it will be more concise and effective. The next step is to do a system shell script.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.